package com.apes.fn.scm.retailOrder.repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.framework.query.ApesDao;
import com.apes.framework.util.DateUtil;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.time.temporal.TemporalAdjusters;
import java.util.Date;

/**
 * @ClassName : ReportDaliyDao
 * @Description : 微信推送接口
 * @Author : zyh
 * @Date: 2020-07-28 10:05
 */
@Component
public class ReportDaliyDao extends ApesDao {

    @Value("${apes.switched:false}")
    private Boolean switched;
    //平台内客户
    public String C_IN_PLATFORM_CLIENT =

            "      FROM ACC_VOUCHER a, ACC_VOUCHER_ITEM B\n" +
            "     WHERE A.ID = B.VOUCHER_ID\n" +
            "       AND A.VOUCHER_TYPE = 'CUSTOMER_SALE'" +
            "       AND A.INVENTORY_MODE = 'IM_OWN'" +
            "       AND nvl(a.classification,' ') not in ('CROSS_IC','UNIT_CLIENT','UNIT_CLIENT_RETURN') \n" +
            "            AND (a.origin_business_type not in ('RetailReturn','ProfitAndLoss') " +
            "            OR (a.origin_business_type = 'RetailReturn' AND a.classification IN('08', '09')))" +
            "       AND B.PRODUCT_ID <> '0002902284'\n" +
            "       AND ((SELECT t.Party_Id\n" +
            "               FROM MAS_PARTY_ROLE t\n" +
            "              WHERE T.ID = A.COMPANY_ID) !=\n" +
            "           (SELECT t.Party_Id\n" +
            "               FROM MAS_PARTY_ROLE t\n" +
            "              WHERE T.ID = A.PARTNER_ID))\n" ;


    /**
     * 查询门店日销售数据
     * @param param
     * @return
     */
    public JSONArray findRCL_DAILYSALESREPORT(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("SELECT " );
        if (param.get("merge")==null)stringBuffer.append("A.COMPANY_ID, D.NAME COMPANY_NAME, A.DEPT_ID,(SELECT NAME FROM MAS_DEPT WHERE ID= A.DEPT_ID) DEPT_NAME," );
        stringBuffer.append("SUM(SALE_MONEY) SALE_MONEY,\n" +
                "       SUM(PAYMENT_MONEY) PAYMENT_MONEY,\n" +
                "       SUM(ACCOUNTS_RECEIVABLEIN) ACCOUNTS_RECEIVABLEIN,\n" +
                "       SUM(ACCOUNTS_RECEIVABLEOUT) ACCOUNTS_RECEIVABLEOUT,\n" +
                "       SUM(YEARCARD) YEARCARD, SUM(ADVANCE_PAYMENT) ADVANCE_PAYMENT,\n" +
                "       SUM(STORED_VALUE) STORED_VALUE, SUM(BUY_YEARCARD) BUY_YEARCARD,\n" +
                "       SUM(STORED_VALUE_RECHARGE) STORED_VALUE_RECHARGE,\n" +
                "       SUM(STORED_VALUE_RECHARGE -STORED_VALUE) STORED_VALUE_AMOUNT,\n" +
                "       SUM(ADVANCE_PAYMENT_RECHARGE -ADVANCE_PAYMENT) ADVANCE_AMOUNT,\n" +

                "       SUM(ADVANCE_PAYMENT_RECHARGE) ADVANCE_PAYMENT_RECHARGE,\n" +
                "       SUM(FIXED_LOSS_COLLECTION) FIXED_LOSS_COLLECTION,\n" +
                "       SUM(CAPITAL_INCOME) CAPITAL_INCOME,\n" +
                "       SUM(CROSS_COMPANY_INCOME) CROSS_COMPANY_INCOME,\n" +
                "       SUM(CROSS_COMPANY_PAY)*-1 CROSS_COMPANY_PAY,\n" +
                "       SUM(SERVICE_PERFORMANCE)-SUM(YEARCARD) SERVICE_PERFORMANCE ,\n" +
                "       SUM(TIMES_OF_ENTERING_THE_STORE) TIMES_OF_ENTERING_THE_STORE,\n" +
                "       SUM(CAR_WASH) CAR_WASH, SUM(CAR_WASH_TIMES) CAR_WASH_TIMES,\n" +
                "       SUM(COSMETOLOGY) COSMETOLOGY,\n" +
                "       SUM(COSMETOLOGY_TIMES) COSMETOLOGY_TIMES, SUM(MAINTAIN) MAINTAIN,\n" +
                "       SUM(MAINTAIN_TIMES) MAINTAIN_TIMES, SUM(TYRE) TYRE,\n" +
                "       SUM(TYRE_TIMES) TYRE_TIMES, SUM(REPAIR) REPAIR,\n" +
                "       SUM(REPAIR_TIMES) REPAIR_TIMES,\n" +
                "       SUM(SHEET_METAL_SPRAY) SHEET_METAL_SPRAY,\n" +
                "       SUM(SHEET_METAL_SPRAY_TIMES) SHEET_METAL_SPRAY_TIMES,\n" +
                "       SUM(REFIT) REFIT, SUM(REFIT_TIMES) REFIT_TIMES,\n" +
                "       SUM(ROAD_RESCUE) ROAD_RESCUE,\n" +
                "       SUM(ROAD_RESCUE_TIMES) ROAD_RESCUE_TIMES, SUM(BOUTIQUE) BOUTIQUE,\n" +
                "       SUM(ARTICLES_FOR_DAILY_USE) ARTICLES_FOR_DAILY_USE,\n" +
                "       SUM(TECHNICAL_INSPECTION) TECHNICAL_INSPECTION,\n" +
                "       SUM(INFORMATION_SERVICE) INFORMATION_SERVICE,\n" +
                "       SUM(FIXED_LOSS_DIRECT_COMPENSATION) FIXED_LOSS_DIRECT_COMPENSATION,\n" +
                "       SUM(STORED_VALUE_RETAIL) STORED_VALUE_RETAIL,\n" +
                "       SUM(INSURANCE_ORDER_AMT) INSURANCE_ORDER_AMT,\n" +
                "       SUM(FN_COMMISSION) FN_COMMISSION,\n" +
                "       SUM(USER_CAR_FEE) USER_CAR_FEE,\n" +
                "       SUM(FN_SETTLE_EXPENS) FN_SETTLE_EXPENS,\n" +
                "       SUM(USERD_CAR_FINAL) USERD_CAR_FINAL,\n" +
                "       SUM(INSURANCE_AMOUNT) INSURANCE_AMOUNT,\n" +
                "       SUM(OUR_LOSS_COLLECTION) OUR_LOSS_COLLECTION,\n" +
                "       SUM(SELF_FIXED_LOSS)+SUM(INNER_LOSS_COLLECTION_MD)+SUM(INNER_LOSS_COLLECTION) FIXED_LOSS_ALL,"+
                "       MAX(FLUSH_DATE) FLUSH_DATE, SUM(INNER_LOSS_COLLECTION_MD) FIXED_LOSS,sum(INNER_LOSS_COLLECTION)INNER_LOSS_COLLECTION,sum(SELF_FIXED_LOSS)SELF_FIXED_LOSS, \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(CAR_WASH)/ SUM(SERVICE_PERFORMANCE))*100,2) END CAR_WASH_RATE,\n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(COSMETOLOGY)/ SUM(SERVICE_PERFORMANCE))*100,2) END COSMETOLOGY_RATE,       \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(MAINTAIN)/ SUM(SERVICE_PERFORMANCE))*100,2) END MAINTAIN_RATE,\n" +

                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(TYRE)/ SUM(SERVICE_PERFORMANCE))*100,2) END TYRE_RATE,       \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(REPAIR)/ SUM(SERVICE_PERFORMANCE))*100,2) END REPAIR_RATE,       \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(SHEET_METAL_SPRAY)/ SUM(SERVICE_PERFORMANCE))*100,2) END SHEET_METAL_SPRAY_RATE,       \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(REFIT)/ SUM(SERVICE_PERFORMANCE))*100,2) END REFIT_RATE,       \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(ROAD_RESCUE)/ SUM(SERVICE_PERFORMANCE))*100,2) END ROAD_RESCUE_RATE,       \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(BOUTIQUE)/ SUM(SERVICE_PERFORMANCE))*100,2) END BOUTIQUE_RATE,       \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(ARTICLES_FOR_DAILY_USE)/ SUM(SERVICE_PERFORMANCE))*100,2) END ARTICLES_FOR_DAILY_USE_RATE,      \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(TECHNICAL_INSPECTION)/ SUM(SERVICE_PERFORMANCE))*100,2) END TECHNICAL_INSPECTION_RATE,       \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(INFORMATION_SERVICE)/ SUM(SERVICE_PERFORMANCE))*100,2) END INFORMATION_SERVICE_RATE   " +
                "  FROM RCL_DAILYSALESREPORT A, MAS_PARTY_ROLE C, MAS_PARTY D ,mas_dept e,MAS_STORE f\n" +
                " WHERE 1 = 1\n" +
                "   AND C.ID = A.COMPANY_ID\n" +
                "   AND C.PARTY_ID = D.ID " +
                "   and e.id =a.DEPT_ID " +
                "   and e.DEPT_TYPE = '000007'" +
                "   AND e.ID = F.DEPT_ID" +
                "   AND f.CLOSING_DAY IS NULL");

        if(param.get("COMPANYID") != null ) stringBuffer.append(" AND A.COMPANY_ID in (:COMPANYID) ");
        if(param.get("DEPTID") != null )  stringBuffer.append(" AND A.DEPT_ID IN :DEPTID ");
        if(param.get("STARTDATE") != null)  stringBuffer.append(" AND A.DATE_OF_STATISTICS >= GET_DATE(:STARTDATE) ");
        if(param.get("ENDDATE") != null)  stringBuffer.append(" AND A.DATE_OF_STATISTICS <= GET_DATE(:ENDDATE) ");
        if (param.get("merge")==null)stringBuffer.append("GROUP BY A.COMPANY_ID, D.NAME, A.DEPT_ID ");
        param.put("data",stringBuffer.toString());
        JSONArray jsonArray = printSql(param);
        if (!jsonArray.isEmpty())return jsonArray;
        return toJSONArray(find(stringBuffer.toString(), param));
    }



    /**
     * 查询门店月销售数据
     * @param param
     * @return
     */
    public JSONArray findRCL_MOnthSALESREPORT(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("SELECT " );
        if (param.get("merge")==null)stringBuffer.append("A.COMPANY_ID, D.NAME COMPANY_NAME,A.DEPT_ID,(SELECT NAME FROM MAS_DEPT WHERE ID= A.DEPT_ID) DEPT_NAME, \n");
        stringBuffer.append("       SUM(SALE_MONEY) SALE_MONEY, SUM(PAYMENT_MONEY) PAYMENT_MONEY,\n" +
                "       SUM(ACCOUNTS_RECEIVABLEIN) ACCOUNTS_RECEIVABLEIN,\n" +
                "       SUM(ACCOUNTS_RECEIVABLEOUT) ACCOUNTS_RECEIVABLEOUT,\n" +
                "       SUM(YEARCARD) YEARCARD, SUM(ADVANCE_PAYMENT) ADVANCE_PAYMENT,\n" +
                "       SUM(STORED_VALUE) STORED_VALUE, SUM(BUY_YEARCARD) BUY_YEARCARD,\n" +
                "       SUM(STORED_VALUE_RECHARGE) STORED_VALUE_RECHARGE,\n" +
                "       SUM(ADVANCE_PAYMENT_RECHARGE) ADVANCE_PAYMENT_RECHARGE,\n" +
                "       SUM(FIXED_LOSS_COLLECTION) FIXED_LOSS_COLLECTION,\n" +
                "       SUM(STORED_VALUE_RECHARGE -STORED_VALUE) STORED_VALUE_AMOUNT,\n" +
                "       SUM(ADVANCE_PAYMENT_RECHARGE -ADVANCE_PAYMENT) ADVANCE_AMOUNT,\n" +
                "       SUM(CAPITAL_INCOME) CAPITAL_INCOME,\n" +
                "       SUM(CROSS_COMPANY_INCOME) CROSS_COMPANY_INCOME,\n" +
                "       SUM(CROSS_COMPANY_PAY)*-1 CROSS_COMPANY_PAY,\n" +
                "       SUM(SERVICE_PERFORMANCE)-SUM(YEARCARD) SERVICE_PERFORMANCE,\n" +
                "       SUM(TIMES_OF_ENTERING_THE_STORE) TIMES_OF_ENTERING_THE_STORE,\n" +
                "       SUM(CAR_WASH) CAR_WASH, SUM(CAR_WASH_TIMES) CAR_WASH_TIMES,\n" +
                "       SUM(COSMETOLOGY) COSMETOLOGY,\n" +
                "       SUM(COSMETOLOGY_TIMES) COSMETOLOGY_TIMES, SUM(MAINTAIN) MAINTAIN,\n" +
                "       SUM(MAINTAIN_TIMES) MAINTAIN_TIMES, SUM(TYRE) TYRE,\n" +
                "       SUM(TYRE_TIMES) TYRE_TIMES, SUM(REPAIR) REPAIR,\n" +
                "       SUM(REPAIR_TIMES) REPAIR_TIMES,\n" +
                "       SUM(SHEET_METAL_SPRAY) SHEET_METAL_SPRAY,\n" +
                "       SUM(SHEET_METAL_SPRAY_TIMES) SHEET_METAL_SPRAY_TIMES,\n" +
                "       SUM(REFIT) REFIT, SUM(REFIT_TIMES) REFIT_TIMES,\n" +
                "       SUM(ROAD_RESCUE) ROAD_RESCUE,\n" +
                "       SUM(ROAD_RESCUE_TIMES) ROAD_RESCUE_TIMES, SUM(BOUTIQUE) BOUTIQUE,\n" +
                "       SUM(ARTICLES_FOR_DAILY_USE) ARTICLES_FOR_DAILY_USE,\n" +
                "       SUM(TECHNICAL_INSPECTION) TECHNICAL_INSPECTION,\n" +
                "       SUM(INFORMATION_SERVICE) INFORMATION_SERVICE,\n" +
                "       SUM(FIXED_LOSS_DIRECT_COMPENSATION) FIXED_LOSS_DIRECT_COMPENSATION,\n" +
                "       SUM(STORED_VALUE_RETAIL) STORED_VALUE_RETAIL,\n" +
                "       SUM(INSURANCE_ORDER_AMT) INSURANCE_ORDER_AMT,\n" +
                "       SUM(FN_COMMISSION) FN_COMMISSION,\n" +
                "       SUM(USER_CAR_FEE) USER_CAR_FEE,\n" +
                "       SUM(FN_SETTLE_EXPENS) FN_SETTLE_EXPENS,\n" +
                "       SUM(USERD_CAR_FINAL) USERD_CAR_FINAL,\n" +
                "       SUM(INSURANCE_AMOUNT) INSURANCE_AMOUNT,\n" +
                "       SUM(OUR_LOSS_COLLECTION) OUR_LOSS_COLLECTION,\n" +
                "       SUM(SELF_FIXED_LOSS)+SUM(INNER_LOSS_COLLECTION_MD)+SUM(INNER_LOSS_COLLECTION) FIXED_LOSS_ALL,"+
                "       SUM(INNER_LOSS_COLLECTION_MD) FIXED_LOSS,sum(INNER_LOSS_COLLECTION)INNER_LOSS_COLLECTION,sum(SELF_FIXED_LOSS)SELF_FIXED_LOSS,\n" +
                "       CASE WHEN SUM(A.SALE_MONEY_LASTMONTH) = 0  THEN 0 ELSE ROUND((SUM(A.SALE_MONEY)-SUM(A.SALE_MONEY_LASTMONTH)) / SUM(A.SALE_MONEY_LASTMONTH)*100,2) END SALE_MONEY_MONTHONMONTH, SUM(A.SALE_MONEY_LASTMONTH) SALE_MONEY_LASTMONTH ,\n" +
                "       CASE WHEN SUM(A.INSURANCE_ORDER_AMT_LASTMONTH) = 0  THEN 0 ELSE ROUND((SUM(A.INSURANCE_ORDER_AMT)-SUM(A.INSURANCE_ORDER_AMT_LASTMONTH)) / SUM(A.INSURANCE_ORDER_AMT_LASTMONTH)*100,2) END INSURANCE_MONTHONMONTH, SUM(A.INSURANCE_ORDER_AMT_LASTMONTH) INSURANCE_ORDER_AMT_LASTMONTH ,\n" +
                "       CASE WHEN SUM(A.USERD_CAR_FINAL_LASTMONTH) = 0  THEN 0 ELSE ROUND((SUM(A.USERD_CAR_FINAL)-SUM(A.USERD_CAR_FINAL_LASTMONTH)) / SUM(A.USERD_CAR_FINAL_LASTMONTH)*100,2) END USERD_CAR_FINAL_MONTHONMONTH, SUM(A.USERD_CAR_FINAL_LASTMONTH) USERD_CAR_FINAL_LASTMONTH ,\n" +
                "       CASE WHEN SUM(A.FN_COMMISSION_LASTMONTH) = 0  THEN 0 ELSE ROUND((SUM(A.FN_COMMISSION)-SUM(A.FN_COMMISSION_LASTMONTH)) / SUM(A.FN_COMMISSION_LASTMONTH)*100,2) END FN_COMMISSION_MONTHONMONTH, SUM(A.FN_COMMISSION_LASTMONTH) FN_COMMISSION_LASTMONTH ,\n" +
                "       CASE WHEN SUM(A.USER_CAR_FEE_LASTMONTH) = 0  THEN 0 ELSE ROUND((SUM(A.USER_CAR_FEE)-SUM(A.USER_CAR_FEE_LASTMONTH)) / SUM(A.USER_CAR_FEE_LASTMONTH)*100,2) END USER_CAR_FEE_MONTHONMONTH, SUM(A.USER_CAR_FEE_LASTMONTH) USER_CAR_FEE_LASTMONTH ,\n" +
                "       CASE WHEN SUM(A.INSURANCE_AMOUNT_LASTMONTH) = 0  THEN 0 ELSE ROUND((SUM(A.INSURANCE_AMOUNT)-SUM(A.INSURANCE_AMOUNT_LASTMONTH)) / SUM(A.INSURANCE_AMOUNT_LASTMONTH)*100,2) END INSURANCE_AMOUNT_MONTHONMONTH, SUM(A.INSURANCE_AMOUNT_LASTMONTH) INSURANCE_AMOUNT_LASTMONTH ,\n" +
                "       CASE WHEN SUM(A.OUR_LOSS_COLLECTION_LASTMONTH) = 0  THEN 0 ELSE ROUND((SUM(A.OUR_LOSS_COLLECTION)-SUM(A.OUR_LOSS_COLLECTION_LASTMONTH)) / SUM(A.OUR_LOSS_COLLECTION_LASTMONTH)*100,2) END OUR_LOSS_MONTHONMONTH, SUM(A.OUR_LOSS_COLLECTION_LASTMONTH) OUR_LOSS_COLLECTION_LASTMONTH ,\n" +
                "       CASE WHEN SUM(A.FN_SETTLE_EXPENS_LASTMONTH) = 0  THEN 0 ELSE ROUND((SUM(A.FN_SETTLE_EXPENS)-SUM(A.FN_SETTLE_EXPENS_LASTMONTH)) / SUM(A.FN_SETTLE_EXPENS_LASTMONTH)*100,2) END FN_SETTLE_EXPENS_MONTHONMONTH, SUM(A.FN_SETTLE_EXPENS_LASTMONTH) FN_SETTLE_EXPENS_LASTMONTH ,\n" +
                "       CASE WHEN SUM(A.SERVICE_PERFORMANCE_LASTMONTH) = 0  THEN 0 ELSE ROUND((SUM(A.SERVICE_PERFORMANCE)-SUM(A.SERVICE_PERFORMANCE_LASTMONTH)) / SUM(A.SERVICE_PERFORMANCE_LASTMONTH)*100,2) END SERVICE_MONTHONMONTH,SUM(A.SERVICE_PERFORMANCE_LASTMONTH) SERVICE_PERFORMANCE_LASTMONTH,\n" +
                "       CASE WHEN SUM(A.BUY_YEARCARD_LASTMONTH) = 0  THEN 0 ELSE ROUND((SUM(A.BUY_YEARCARD)-SUM(A.BUY_YEARCARD_LASTMONTH)) / SUM(A.BUY_YEARCARD_LASTMONTH)*100,2) END BUY_YEARCARD_MONTHONMONTH,SUM(A.BUY_YEARCARD_LASTMONTH) BUY_YEARCARD_LASTMONTH,\n" +
                "       CASE WHEN SUM(A.FIXED_LOSS_LASTMONTH) = 0  THEN 0 ELSE ROUND((SUM(A.SELF_FIXED_LOSS + INNER_LOSS_COLLECTION)+SUM(A.INNER_LOSS_COLLECTION_MD)) / SUM(A.FIXED_LOSS_LASTMONTH)*100,2) END FIXED_LOSS_MONTHONMONTH, SUM(A.FIXED_LOSS_LASTMONTH) FIXED_LOSS_LASTMONTH,\n" +
                "       CASE WHEN SUM(A.TIMES_LASTMONTH) = 0  THEN 0 ELSE ROUND((SUM(A.TIMES_OF_ENTERING_THE_STORE)-SUM(A.TIMES_LASTMONTH)) / SUM(A.TIMES_LASTMONTH)*100,2) END TIMES_MONTHONMONTH,SUM(A.TIMES_LASTMONTH) TIMES_LASTMONTH,\n" +

                "       CASE WHEN SUM(A.CAR_WASH_LASTMONTH) = 0  THEN 0 ELSE ROUND((SUM(A.CAR_WASH)-SUM(A.CAR_WASH_LASTMONTH)) / SUM(A.CAR_WASH_LASTMONTH)*100,2) END CAR_WASH_MONTHONMONTH, SUM(A.CAR_WASH_LASTMONTH) CAR_WASH_LASTMONTH,\n" +
                "       CASE WHEN SUM(A.COSMETOLOGY_LASTMONTH) = 0  THEN 0 ELSE ROUND((SUM(A.COSMETOLOGY)-SUM(A.COSMETOLOGY_LASTMONTH)) / SUM(A.COSMETOLOGY_LASTMONTH)*100,2) END COSMETOLOGY_MONTHONMONTH,SUM(A.COSMETOLOGY_LASTMONTH) COSMETOLOGY_LASTMONTH, \n" +
                "       CASE WHEN SUM(A.MAINTAIN_LASTMONTH) = 0  THEN 0 ELSE ROUND((SUM(A.MAINTAIN)-SUM(A.MAINTAIN_LASTMONTH)) / SUM(A.MAINTAIN_LASTMONTH)*100,2) END MAINTAIN_MONTHONMONTH, SUM(A.MAINTAIN_LASTMONTH) MAINTAIN_LASTMONTH,\n" +
                "       CASE WHEN SUM(A.TYRE_LASTMONTH) = 0  THEN 0 ELSE ROUND((SUM(A.TYRE_LASTMONTH)-SUM(A.TYRE_LASTMONTH)) / SUM(A.TYRE_LASTMONTH)*100,2) END TYRE_MONTHONMONTH,SUM(A.TYRE_LASTMONTH) TYRE_LASTMONTH ,\n" +
                "       CASE WHEN SUM(A.REPAIR_LASTMONTH) = 0  THEN 0 ELSE ROUND((SUM(A.REPAIR)-SUM(A.REPAIR_LASTMONTH)) / SUM(A.REPAIR_LASTMONTH)*100,2) END REPAIR_MONTHONMONTH,SUM(A.REPAIR_LASTMONTH) REPAIR_LASTMONTH,\n" +
                "       CASE WHEN SUM(A.SHEET_METAL_SPRAY_LASTMONTH) = 0  THEN 0 ELSE ROUND((SUM(A.SHEET_METAL_SPRAY)-SUM(A.SHEET_METAL_SPRAY_LASTMONTH)) / SUM(A.SHEET_METAL_SPRAY_LASTMONTH)*100,2) END SHEET_METAL_SPRAY_MONTHONMONTH, SUM(A.SHEET_METAL_SPRAY_LASTMONTH) SHEET_METAL_SPRAY_LASTMONTH ,\n" +
                "       CASE WHEN SUM(A.REFIT_LASTMONTH) = 0  THEN 0 ELSE ROUND((SUM(A.REFIT)-SUM(A.REFIT_LASTMONTH)) / SUM(A.REFIT_LASTMONTH)*100,2) END REFIT_MONTHONMONTH,SUM(A.REFIT_LASTMONTH) REFIT_LASTMONTH ,\n" +
                "       CASE WHEN SUM(A.ROAD_RESCUE_LASTMONTH) = 0  THEN 0 ELSE ROUND((SUM(A.ROAD_RESCUE)-SUM(A.ROAD_RESCUE_LASTMONTH)) / SUM(A.ROAD_RESCUE_LASTMONTH)*100,2) END ROAD_RESCUE_MONTHONMONTH, SUM(A.ROAD_RESCUE_LASTMONTH) ROAD_RESCUE_LASTMONTH,\n" +
                "       CASE WHEN SUM(A.BOUTIQUE_LASTMONTH) = 0  THEN 0 ELSE ROUND((SUM(A.BOUTIQUE)-SUM(A.BOUTIQUE_LASTMONTH)) / SUM(A.BOUTIQUE_LASTMONTH)*100,2) END BOUTIQUE_MONTHONMONTH,SUM(A.BOUTIQUE_LASTMONTH) BOUTIQUE_LASTMONTH, \n" +
                "       CASE WHEN SUM(A.ARTICLES_FOR_LASTMONTH) = 0  THEN 0 ELSE ROUND((SUM(A.ARTICLES_FOR_DAILY_USE)-SUM(A.ARTICLES_FOR_LASTMONTH)) / SUM(A.ARTICLES_FOR_LASTMONTH)*100,2) END ARTICLES_MONTHONMONTH,SUM(A.ARTICLES_FOR_LASTMONTH) ARTICLES_FOR_LASTMONTH ,\n" +
                "       CASE WHEN SUM(A.TECHNICAL_INSPECTION_LASTMONTH) = 0  THEN 0 ELSE ROUND((SUM(A.TECHNICAL_INSPECTION)-SUM(A.TECHNICAL_INSPECTION_LASTMONTH)) / SUM(A.TECHNICAL_INSPECTION_LASTMONTH)*100,2) END TECHNICAL_MONTHONMONTH,SUM(A.TECHNICAL_INSPECTION_LASTMONTH) TECHNICAL_INSPECTION_LASTMONTH, \n" +
                "       CASE WHEN SUM(A.INFORMATION_SERVICE_LASTMONTH) = 0  THEN 0 ELSE ROUND((SUM(A.INFORMATION_SERVICE)-SUM(A.INFORMATION_SERVICE_LASTMONTH)) / SUM(A.INFORMATION_SERVICE_LASTMONTH)*100,2) END INFORMATION_MONTHONMONTH, SUM(A.INFORMATION_SERVICE_LASTMONTH) INFORMATION_SERVICE_LASTMONTH ,     \n" +

                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(CAR_WASH)/ SUM(SERVICE_PERFORMANCE))*100,2) END CAR_WASH_RATE,\n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(COSMETOLOGY)/ SUM(SERVICE_PERFORMANCE))*100,2) END COSMETOLOGY_RATE,       \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(MAINTAIN)/ SUM(SERVICE_PERFORMANCE))*100,2) END MAINTAIN_RATE,\n" +

                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(TYRE)/ SUM(SERVICE_PERFORMANCE))*100,2) END TYRE_RATE,       \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(REPAIR)/ SUM(SERVICE_PERFORMANCE))*100,2) END REPAIR_RATE,       \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(SHEET_METAL_SPRAY)/ SUM(SERVICE_PERFORMANCE))*100,2) END SHEET_METAL_SPRAY_RATE,       \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(REFIT)/ SUM(SERVICE_PERFORMANCE))*100,2) END REFIT_RATE,       \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(ROAD_RESCUE)/ SUM(SERVICE_PERFORMANCE))*100,2) END ROAD_RESCUE_RATE,       \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(BOUTIQUE)/ SUM(SERVICE_PERFORMANCE))*100,2) END BOUTIQUE_RATE,       \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(ARTICLES_FOR_DAILY_USE)/ SUM(SERVICE_PERFORMANCE))*100,2) END ARTICLES_FOR_DAILY_USE_RATE,      \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(TECHNICAL_INSPECTION)/ SUM(SERVICE_PERFORMANCE))*100,2) END TECHNICAL_INSPECTION_RATE,       \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(INFORMATION_SERVICE)/ SUM(SERVICE_PERFORMANCE))*100,2) END INFORMATION_SERVICE_RATE," +
                "       (SUM(A.SERVICE_PERFORMANCE_LASTMONTH) -\n" +
                "        NVL("+mergePacke("YEARCARD_LESS",param)+",0)\n" +
                "           )                                                             SERVICE_LASTMONTH_LESS, " +
                "       CASE\n" +
                "           WHEN (SUM(A.SERVICE_PERFORMANCE_LASTMONTH) - NVL("+mergePacke("YEARCARD_LESS",param)+",0)) = 0 THEN 0\n" +
                "           ELSE ROUND(\n" +
                "                           (\n" +
                "                                   (SUM(SERVICE_PERFORMANCE) - SUM(YEARCARD)) -\n" +
                "                                   (SUM(A.SERVICE_PERFORMANCE_LASTMONTH) - NVL("+mergePacke("YEARCARD_LESS",param)+",0)\n" +
                "                                       )\n" +
                "                               ) /\n" +
                "                           (SUM(A.SERVICE_PERFORMANCE_LASTMONTH) - NVL("+mergePacke("YEARCARD_LESS",param)+",0)\n" +
                "                               ) * 100, 2\n" +
                "               ) END                                                               SERVICE_MONTHONMONTH_LESS     " +
                "  FROM RCL_DAILYSALESREPORT_MONTH A" +
                "   LEFT JOIN (select COMPANY_ID,SUM(YEARCARD)YEARCARD_LESS ,DEPT_ID\n" +
                "     from RCL_DAILYSALESREPORT s\n" +
                "     where s.DATE_OF_STATISTICS >= :LAST_MONTH_FIRST\n" +
                "       AND s.DATE_OF_STATISTICS <= :LAST_MONTH_LAST\n" +
                "         group by COMPANY_ID, DEPT_ID) M on m.COMPANY_ID = A.COMPANY_ID and m.DEPT_ID = A.DEPT_ID,  MAS_PARTY_ROLE C, MAS_PARTY D,MAS_DEPT E ,MAS_STORE f\n" +
                " WHERE 1 = 1  \n" +
                "   AND C.ID = A.COMPANY_ID \n" +
                "   AND C.PARTY_ID = D.ID " +
                "   AND E.ID = A.DEPT_ID" +
                "   and e.DEPT_TYPE = '000007'" +
                "   AND e.ID = F.DEPT_ID" +
                "   AND f.CLOSING_DAY IS NULL");

        if(param.get("COMPANYID") != null ) stringBuffer.append(" AND A.COMPANY_ID in :COMPANYID ");
        if(param.get("DEPTID") != null )  stringBuffer.append(" AND A.DEPT_ID in :DEPTID ");
        if(param.get("YEAR_AND_MONTH") != null)  stringBuffer.append(" AND A.YEAR_AND_MONTH = :YEAR_AND_MONTH ");
        if (param.get("merge")==null)stringBuffer.append("GROUP BY A.COMPANY_ID, D.NAME , A.DEPT_ID ");
        if (param.get("merge")!=null)stringBuffer.append("GROUP BY A.YEAR_AND_MONTH ");
        param.put("data",stringBuffer.toString());
        JSONArray jsonArray = printSql(param);
        if (!jsonArray.isEmpty())return jsonArray;
        return toJSONArray(find(stringBuffer.toString(), param));
    }


    private String mergePacke(String sql,JSONObject param){
        if (param.get("YEARCARD_LESS")!=null)return "sum("+sql+")";
        if (param.get("merge")==null)return sql;
        return "sum("+sql+")";
    }
    /**
     * 获取公司服务业绩排名
     *
     * @param param
     * @return
     */
    public JSONArray getDayRank(JSONObject param) {

        StringBuffer stringBuffer = new StringBuffer();

        stringBuffer.append("SELECT DEPTID,\n" +
                "       totalRank,\n" +
                "       DEPTNAME,\n" +
                "       SALE_MONEY,   \n   " +
                "       SERVICE_PERFORMANCE,\n" +
                "       serverRank,\n" +
                "       TIMES_OF_ENTERING_THE_STORE,\n" +
                "       (nvl((select totalRank\n" +
                "             from (SELECT DEPTID, totalRank, DEPTNAME, SALE_MONEY\n" +
                "                   FROM (SELECT  B.ID  DEPTID,B.NAME  DEPTNAME,\n" +
                "                                ROW_NUMBER() OVER (ORDER BY (SUM(A.SALE_MONEY)) DESC) totalRank,\n" +
                "                                SUM(A.SALE_MONEY)   SALE_MONEY\n" +
                "                         FROM RCL_DAILYSALESREPORT A,\n" +
                "                              MAS_DEPT B,\n" +
                "                              MAS_PARTY_ROLE C,\n" +
                "                              MAS_PARTY D\n" +
                "                         WHERE A.DEPT_ID = B.ID\n" +
                "                           AND C.ID = A.COMPANY_ID\n" +
                "                           AND C.PARTY_ID = D.ID\n" +
                "                           AND DATE_OF_STATISTICS = GET_DATE(:DATE_OF_STATISTICS) - 1\n" +
                "                         GROUP BY  B.ID, B.NAME)) X\n" +
                "             where X.DEPTID = A.DEPTID), totalRank) - totalRank) YESTERDAY_RANK,  (nvl((select serverRank\n" +
                "             from (SELECT DEPTID, serverRank, DEPTNAME, SERVICE_PERFORMANCE\n" +
                "                   FROM (SELECT B.ID                                                  DEPTID,\n" +
                "                                B.NAME                                                DEPTNAME,\n" +
                "                                ROW_NUMBER() OVER (ORDER BY (SUM(A.SALE_MONEY -FN_SETTLE_EXPENS +INNER_LOSS_COLLECTION) ) DESC) serverRank,\n" +
                "                                SUM(A.SALE_MONEY -FN_SETTLE_EXPENS +INNER_LOSS_COLLECTION)                                    SERVICE_PERFORMANCE\n" +
                "                         FROM RCL_DAILYSALESREPORT A,\n" +
                "                              MAS_DEPT B,\n" +
                "                              MAS_PARTY_ROLE C,\n" +
                "                              MAS_PARTY D\n" +
                "                         WHERE A.DEPT_ID = B.ID\n" +
                "                           AND C.ID = A.COMPANY_ID\n" +
                "                           AND C.PARTY_ID = D.ID\n" +
                "                           AND DATE_OF_STATISTICS = GET_DATE(:DATE_OF_STATISTICS) - 1\n" +
                "                         GROUP BY  B.ID, B.NAME)) X\n" +
                "             where X.DEPTID = A.DEPTID), serverRank) - serverRank) YESTER_DAY_SERVER_RANK\n" +
                "FROM (SELECT A.COMPANY_ID,D.NAME, B.ID DEPTID, B.NAME DEPTNAME,\n" +
                "             ROW_NUMBER() OVER (ORDER BY (SUM(A.SALE_MONEY)) DESC) totalRank,\n" +
                "             SUM(A.SALE_MONEY)  SALE_MONEY," +
                " SUM(A.SALE_MONEY -FN_SETTLE_EXPENS +INNER_LOSS_COLLECTION)                                     SERVICE_PERFORMANCE,\n" +
                " SUM(TIMES_OF_ENTERING_THE_STORE) TIMES_OF_ENTERING_THE_STORE,\n" +
                "             ROW_NUMBER() OVER (ORDER BY (SUM(A.SALE_MONEY -FN_SETTLE_EXPENS +INNER_LOSS_COLLECTION) ) DESC) serverRank\n" +
                "      FROM RCL_DAILYSALESREPORT A,\n" +
                "           MAS_DEPT B,\n" +
                "           MAS_PARTY_ROLE C,\n" +
                "           MAS_PARTY D," +
                "           MAS_STORE E" +
                "           \n" +
                "      WHERE 1 = 1\n" +
                "        AND A.DEPT_ID = B.ID" +
                "        and b.DEPT_TYPE = '000007'\n" +
                "        AND C.ID = A.COMPANY_ID\n" +
                "        AND C.PARTY_ID = D.ID" +
                "        AND B.ID = E.DEPT_ID \n") ;


        if(param.get("DATE_OF_STATISTICS") != null ) stringBuffer.append("AND  DATE_OF_STATISTICS =  GET_DATE(:DATE_OF_STATISTICS)  ");
        if(param.get("DATE_OF_STATISTICS") != null ) stringBuffer.append("AND  (trunc(E.CLOSING_DAY) >= GET_DATE(:DATE_OF_STATISTICS) OR E.CLOSING_DAY IS NULL)  ");
        if(param.get("DATE_OF_STATISTICS") == null ) stringBuffer.append("AND E.CLOSING_DAY IS NULL  ");
        stringBuffer.append("    GROUP BY A.COMPANY_ID,D.NAME,B.ID,B.NAME)A WHERE 1=1 ");

        if(param.get("COMPANY_ID") != null ) stringBuffer.append(" AND COMPANY_ID in :COMPANY_ID  ");
        if(param.get("DEPTID") != null )  stringBuffer.append(" AND DEPTID in :DEPTID ");
        param.put("data",stringBuffer.toString());
        JSONArray jsonArray = printSql(param);
        if (!jsonArray.isEmpty())return jsonArray;
        return toJSONArray(find(stringBuffer.toString(), param));
    }
    /**
     * 获取公司服务业绩排名(周度)
     *
     * @param param
     * @return
     */
    public JSONArray getWeekRank(JSONObject param) {

        StringBuffer stringBuffer = new StringBuffer();

        stringBuffer.append("SELECT DEPTID,\n" +
                "       totalRank,\n" +
                "       DEPTNAME,\n" +
                "       SALE_MONEY,   \n   " +
                "       SERVICE_PERFORMANCE,\n" +
                "       serverRank,\n" +
                "       TIMES_OF_ENTERING_THE_STORE,\n" +
                "       (nvl((select totalRank\n" +
                "             from (SELECT DEPTID, totalRank, DEPTNAME, SALE_MONEY\n" +
                "                   FROM (SELECT A.COMPANY_ID,D.NAME, B.ID  DEPTID,B.NAME  DEPTNAME,\n" +
                "                                ROW_NUMBER() OVER (ORDER BY (SUM(A.SALE_MONEY)) DESC) totalRank,\n" +
                "                                SUM(A.SALE_MONEY)   SALE_MONEY\n" +
                "                         FROM ("+getRCL_DAILYSALESREPORTView(param,"LAST_START_DATE","LAST_END_DATE")+") A,\n" +
                "                              MAS_DEPT B,\n" +
                "                              MAS_PARTY_ROLE C,\n" +
                "                              MAS_PARTY D\n" +
                "                         WHERE A.DEPT_ID = B.ID\n" +
                "                           AND C.ID = A.COMPANY_ID\n" +
                "                           AND C.PARTY_ID = D.ID\n" +
                "                         GROUP BY A.COMPANY_ID, D.NAME, B.ID, B.NAME)) X\n" +
                "             where X.DEPTID = A.DEPTID), totalRank) - totalRank) YESTERDAY_RANK,  (nvl((select serverRank\n" +
                "             from (SELECT DEPTID, serverRank, DEPTNAME, SERVICE_PERFORMANCE\n" +
                "                   FROM (SELECT A.COMPANY_ID,\n" +
                "                                D.NAME,\n" +
                "                                B.ID                                                  DEPTID,\n" +
                "                                B.NAME                                                DEPTNAME,\n" +
                "                                ROW_NUMBER() OVER (ORDER BY (SUM(A.SALE_MONEY -FN_SETTLE_EXPENS +INNER_LOSS_COLLECTION) ) DESC) serverRank,\n" +
                "                                SUM(A.SALE_MONEY -FN_SETTLE_EXPENS +INNER_LOSS_COLLECTION)                                    SERVICE_PERFORMANCE\n" +
                "                         FROM ("+getRCL_DAILYSALESREPORTView(param,"LAST_START_DATE","LAST_END_DATE")+") A,\n" +
                "                              MAS_DEPT B,\n" +
                "                              MAS_PARTY_ROLE C,\n" +
                "                              MAS_PARTY D\n" +
                "                         WHERE A.DEPT_ID = B.ID\n" +
                "                           AND C.ID = A.COMPANY_ID\n" +
                "                           AND C.PARTY_ID = D.ID\n" +
                "                         GROUP BY A.COMPANY_ID, D.NAME, B.ID, B.NAME)) X\n" +
                "             where X.DEPTID = A.DEPTID), serverRank) - serverRank) YESTER_DAY_SERVER_RANK\n" +
                "FROM (SELECT A.COMPANY_ID,D.NAME, B.ID DEPTID, B.NAME DEPTNAME,\n" +
                "             ROW_NUMBER() OVER (ORDER BY (SUM(A.SALE_MONEY)) DESC) totalRank,\n" +
                "             SUM(A.SALE_MONEY)  SALE_MONEY," +
                " SUM(A.SALE_MONEY -FN_SETTLE_EXPENS +INNER_LOSS_COLLECTION)                                     SERVICE_PERFORMANCE,\n" +
                " SUM(TIMES_OF_ENTERING_THE_STORE) TIMES_OF_ENTERING_THE_STORE,\n" +
                "             ROW_NUMBER() OVER (ORDER BY (SUM(A.SALE_MONEY -FN_SETTLE_EXPENS +INNER_LOSS_COLLECTION) ) DESC) serverRank\n" +
                "      FROM ("+getRCL_DAILYSALESREPORTView(param,"STARTDATE","ENDDATE")+") A,\n" +
                "           MAS_DEPT B,\n" +
                "           MAS_PARTY_ROLE C,\n" +
                "           MAS_PARTY D," +
                "           MAS_STORE E" +
                "           \n" +
                "      WHERE 1 = 1\n" +
                "        AND A.DEPT_ID = B.ID" +
                "        and b.DEPT_TYPE = '000007'\n" +
                "        AND C.ID = A.COMPANY_ID\n" +
                "        AND C.PARTY_ID = D.ID" +
                "        AND B.ID = E.DEPT_ID \n") ;


//        if(param.get("DATE_OF_STATISTICS") != null ) stringBuffer.append("AND  DATE_OF_STATISTICS =  GET_DATE(:DATE_OF_STATISTICS)  ");
        if(param.get("STARTDATE") != null ) stringBuffer.append("AND  (trunc(E.CLOSING_DAY) >= :STARTDATE OR E.CLOSING_DAY IS NULL)  ");
        if(param.get("STARTDATE") == null ) stringBuffer.append("AND E.CLOSING_DAY IS NULL  ");
        stringBuffer.append("    GROUP BY A.COMPANY_ID,D.NAME,B.ID,B.NAME)A WHERE 1=1 ");

        if(param.get("COMPANY_ID") != null ) stringBuffer.append(" AND COMPANY_ID in :COMPANY_ID  ");
        if(param.get("DEPTID") != null )  stringBuffer.append(" AND DEPTID in :DEPTID ");
        param.put("data",stringBuffer.toString());
        JSONArray jsonArray = printSql(param);
        if (!jsonArray.isEmpty())return jsonArray;
        return toJSONArray(find(stringBuffer.toString(), param));
    }

    /**
     * 获取公司月度销售排名
     * @param param
     * @return
     */
    public JSONArray getMonthRank(JSONObject param) {

        StringBuffer stringBuffer = new StringBuffer();

        stringBuffer.append("SELECT DEPTID, totalRank, serverRank,DEPTNAME,SALE_MONEY,SERVICE_PERFORMANCE,TIMES_OF_ENTERING_THE_STORE\n");
        if(param.get("YEAR_AND_MONTH") != null ) stringBuffer.append("       ,(nvl((select totalRank\n" +
                "             from (SELECT DEPTID, totalRank, DEPTNAME, SALE_MONEY\n" +
                "                   FROM (SELECT A.COMPANY_ID,\n" +
                "                                D.NAME,\n" +
                "                                B.ID                                                  DEPTID,\n" +
                "                                B.NAME                                                DEPTNAME,\n" +
                "                                ROW_NUMBER() OVER (ORDER BY (SUM(A.SALE_MONEY)) DESC) totalRank,\n" +
                "                                SUM(A.SALE_MONEY)                                     SALE_MONEY\n" +
                "\n" +
                "                         FROM RCL_DAILYSALESREPORT_MONTH A,\n" +
                "                              MAS_DEPT B,\n" +
                "                              MAS_PARTY_ROLE C,\n" +
                "                              MAS_PARTY D\n" +
                "                         WHERE A.DEPT_ID = B.ID\n" +
                "                           AND C.ID = A.COMPANY_ID\n" +
                "                           AND C.PARTY_ID = D.ID AND YEAR_AND_MONTH = :YEAR_AND_MONTH-1\n" +
                "                         GROUP BY A.COMPANY_ID, D.NAME, B.ID, B.NAME)) X\n" +
                "             where X.DEPTID = A.DEPTID), totalRank) - totalRank) YESTERDAY_RANK,  (nvl((select serverRank\n" +
                "             from (SELECT DEPTID,\n" +
                "                          serverRank,\n" +
                "                          DEPTNAME,\n" +
                "                          SERVICE_PERFORMANCE\n" +
                "                   FROM (SELECT A.COMPANY_ID,\n" +
                "                                D.NAME,\n" +
                "                                B.ID                                                  DEPTID,\n" +
                "                                B.NAME\n" +
                "                                                                                      DEPTNAME,\n" +
                "                                ROW_NUMBER() OVER (ORDER BY (SUM(A.SALE_MONEY -FN_SETTLE_EXPENS +INNER_LOSS_COLLECTION) ) DESC) serverRank,\n" +
                "                                SUM(A.SALE_MONEY -FN_SETTLE_EXPENS +INNER_LOSS_COLLECTION) \n" +
                "                                                                                      SERVICE_PERFORMANCE\n" +
                "                         FROM RCL_DAILYSALESREPORT_MONTH A,\n" +
                "                              MAS_DEPT B,\n" +
                "                              MAS_PARTY_ROLE C,\n" +
                "                              MAS_PARTY D\n" +
                "                         WHERE A.DEPT_ID = B.ID\n" +
                "                           AND C.ID = A.COMPANY_ID\n" +
                "                           AND C.PARTY_ID = D.ID\n" +
                "                           AND YEAR_AND_MONTH = :YEAR_AND_MONTH-1\n" +
                "                         GROUP BY A.COMPANY_ID, D.NAME, B.ID, B.NAME)) X\n" +
                "             where X.DEPTID = A.DEPTID), serverRank) - serverRank)\n" +
                "           YESTERDAY_SERVICE_RANK  ");
        stringBuffer.append(     "  FROM (SELECT A.COMPANY_ID,D.NAME, B.ID DEPTID,B.NAME DEPTNAME,\n" +
                "                ROW_NUMBER() OVER(ORDER BY(SUM(A.SALE_MONEY)) DESC) totalRank,\n" +
                "                SUM(A.SALE_MONEY) SALE_MONEY,\n" +
                "                SUM(A.SALE_MONEY -FN_SETTLE_EXPENS +INNER_LOSS_COLLECTION)     SERVICE_PERFORMANCE,\n" +
                "                SUM(TIMES_OF_ENTERING_THE_STORE) TIMES_OF_ENTERING_THE_STORE,\n" +
                "                ROW_NUMBER() OVER(ORDER BY(SUM(A.SALE_MONEY -FN_SETTLE_EXPENS +INNER_LOSS_COLLECTION) ) DESC) serverRank\n" +
                "               \n" +
                "           FROM RCL_DAILYSALESREPORT_MONTH A, MAS_DEPT B, MAS_PARTY_ROLE C,\n" +
                "                MAS_PARTY D ,MAS_STORE E\n" +
                "          WHERE 1 = 1 \n" +
                "            AND A.DEPT_ID = B.ID \n" +
                "            AND C.ID = A.COMPANY_ID \n" +
                "            AND C.PARTY_ID = D.ID" +
                "            and b.DEPT_TYPE = '000007'" +
                "            AND B.VALID = 1\n" +
                "            AND B.ID = E.DEPT_ID \n");


        if(param.get("YEAR_AND_MONTH") != null ) stringBuffer.append("AND  YEAR_AND_MONTH = :YEAR_AND_MONTH  ");
        if(param.get("YEAR_AND_MONTH") != null ) stringBuffer.append("AND  (trunc(E.CLOSING_DAY,'MM') >= TO_DATE(:YEAR_AND_MONTH,'YYYYMM') OR E.CLOSING_DAY IS NULL)  ");
        if(param.get("YEAR_AND_MONTH") == null ) stringBuffer.append("AND E.CLOSING_DAY IS NULL  ");

        stringBuffer.append("    GROUP BY A.COMPANY_ID,D.NAME,B.ID,B.NAME)A WHERE 1=1 ");

        if(param.get("COMPANY_ID") != null ) stringBuffer.append(" AND COMPANY_ID in :COMPANY_ID  ");
        if(param.get("DEPTID") != null )  stringBuffer.append(" AND DEPTID in :DEPTID ");
        param.put("data",stringBuffer.toString());
        JSONArray jsonArray = printSql(param);
        if (!jsonArray.isEmpty())return jsonArray;
        return toJSONArray(find(stringBuffer.toString(), param));
    }





    /**
     * 查询门店日销售数据,每天
     * @param param
     * @return
     */
    public JSONArray findRCL_DAILYSALESREPORT_ALL(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("SELECT A.DATE_OF_STATISTICS,");
        if (param.get("merge")==null)stringBuffer.append("A.COMPANY_ID, A.DEPT_ID,(SELECT NAME FROM MAS_DEPT WHERE ID= A.DEPT_ID) DEPT_NAME, D.NAME COMPANY_NAME, ");
        stringBuffer.append("SUM(SALE_MONEY) SALE_MONEY,\n" +
                "       SUM(PAYMENT_MONEY) PAYMENT_MONEY,\n" +
                "       SUM(ACCOUNTS_RECEIVABLEIN) ACCOUNTS_RECEIVABLEIN,\n" +
                "       SUM(ACCOUNTS_RECEIVABLEOUT) ACCOUNTS_RECEIVABLEOUT,\n" +
                "       SUM(YEARCARD) YEARCARD, SUM(ADVANCE_PAYMENT) ADVANCE_PAYMENT,\n" +
                "       SUM(STORED_VALUE) STORED_VALUE, SUM(BUY_YEARCARD) BUY_YEARCARD,\n" +
                "       SUM(STORED_VALUE_RECHARGE) STORED_VALUE_RECHARGE,\n" +
                "       SUM(STORED_VALUE_RECHARGE -STORED_VALUE) STORED_VALUE_AMOUNT,\n" +
                "       SUM(ADVANCE_PAYMENT_RECHARGE -ADVANCE_PAYMENT) ADVANCE_AMOUNT,\n" +

                "       SUM(ADVANCE_PAYMENT_RECHARGE) ADVANCE_PAYMENT_RECHARGE,\n" +
                "       SUM(FIXED_LOSS_COLLECTION) FIXED_LOSS_COLLECTION,\n" +
                "       SUM(CAPITAL_INCOME) CAPITAL_INCOME,\n" +
                "       SUM(SERVICE_PERFORMANCE)-SUM(YEARCARD) SERVICE_PERFORMANCE,\n" +
                "       SUM(TIMES_OF_ENTERING_THE_STORE) TIMES_OF_ENTERING_THE_STORE,\n" +
                "       SUM(CAR_WASH) CAR_WASH, SUM(CAR_WASH_TIMES) CAR_WASH_TIMES,\n" +
                "       SUM(COSMETOLOGY) COSMETOLOGY,\n" +
                "       SUM(CROSS_COMPANY_INCOME) CROSS_COMPANY_INCOME,\n" +
                "       SUM(CROSS_COMPANY_PAY)*-1 CROSS_COMPANY_PAY,\n" +
                "       SUM(COSMETOLOGY_TIMES) COSMETOLOGY_TIMES, SUM(MAINTAIN) MAINTAIN,\n" +
                "       SUM(MAINTAIN_TIMES) MAINTAIN_TIMES, SUM(TYRE) TYRE,\n" +
                "       SUM(TYRE_TIMES) TYRE_TIMES, SUM(REPAIR) REPAIR,\n" +
                "       SUM(REPAIR_TIMES) REPAIR_TIMES,\n" +
                "       SUM(SHEET_METAL_SPRAY) SHEET_METAL_SPRAY,\n" +
                "       SUM(SHEET_METAL_SPRAY_TIMES) SHEET_METAL_SPRAY_TIMES,\n" +
                "       SUM(REFIT) REFIT, SUM(REFIT_TIMES) REFIT_TIMES,\n" +
                "       SUM(ROAD_RESCUE) ROAD_RESCUE,\n" +
                "       SUM(ROAD_RESCUE_TIMES) ROAD_RESCUE_TIMES, SUM(BOUTIQUE) BOUTIQUE,\n" +
                "       SUM(ARTICLES_FOR_DAILY_USE) ARTICLES_FOR_DAILY_USE,\n" +
                "       SUM(TECHNICAL_INSPECTION) TECHNICAL_INSPECTION,\n" +
                "       SUM(INFORMATION_SERVICE) INFORMATION_SERVICE,\n" +
                "       SUM(FIXED_LOSS_DIRECT_COMPENSATION) FIXED_LOSS_DIRECT_COMPENSATION,\n" +
                "       SUM(STORED_VALUE_RETAIL) STORED_VALUE_RETAIL,\n" +
                "       SUM(INSURANCE_ORDER_AMT) INSURANCE_ORDER_AMT,\n" +
                "       SUM(FN_COMMISSION) FN_COMMISSION,\n" +
                "       SUM(USER_CAR_FEE) USER_CAR_FEE,\n" +
                "       SUM(FN_SETTLE_EXPENS) FN_SETTLE_EXPENS,\n" +
                "       SUM(USERD_CAR_FINAL) USERD_CAR_FINAL,\n" +
                "       SUM(INSURANCE_AMOUNT) INSURANCE_AMOUNT,\n" +
                "       SUM(OUR_LOSS_COLLECTION) OUR_LOSS_COLLECTION," +
                "       SUM(SELF_FIXED_LOSS)+SUM(INNER_LOSS_COLLECTION_MD)+SUM(INNER_LOSS_COLLECTION) FIXED_LOSS_ALL,\n" +
                "       MAX(FLUSH_DATE) FLUSH_DATE, SUM(INNER_LOSS_COLLECTION_MD) FIXED_LOSS,sum(INNER_LOSS_COLLECTION)INNER_LOSS_COLLECTION,sum(SELF_FIXED_LOSS)SELF_FIXED_LOSS, \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(CAR_WASH)/ SUM(SERVICE_PERFORMANCE))*100,2) END CAR_WASH_RATE,\n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(COSMETOLOGY)/ SUM(SERVICE_PERFORMANCE))*100,2) END COSMETOLOGY_RATE,       \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(MAINTAIN)/ SUM(SERVICE_PERFORMANCE))*100,2) END MAINTAIN_RATE,\n" +

                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(TYRE)/ SUM(SERVICE_PERFORMANCE))*100,2) END TYRE_RATE,       \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(REPAIR)/ SUM(SERVICE_PERFORMANCE))*100,2) END REPAIR_RATE,       \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(SHEET_METAL_SPRAY)/ SUM(SERVICE_PERFORMANCE))*100,2) END SHEET_METAL_SPRAY_RATE,       \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(REFIT)/ SUM(SERVICE_PERFORMANCE))*100,2) END REFIT_RATE,       \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(ROAD_RESCUE)/ SUM(SERVICE_PERFORMANCE))*100,2) END ROAD_RESCUE_RATE,       \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(BOUTIQUE)/ SUM(SERVICE_PERFORMANCE))*100,2) END BOUTIQUE_RATE,       \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(ARTICLES_FOR_DAILY_USE)/ SUM(SERVICE_PERFORMANCE))*100,2) END ARTICLES_FOR_DAILY_USE_RATE,      \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(TECHNICAL_INSPECTION)/ SUM(SERVICE_PERFORMANCE))*100,2) END TECHNICAL_INSPECTION_RATE,       \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(INFORMATION_SERVICE)/ SUM(SERVICE_PERFORMANCE))*100,2) END INFORMATION_SERVICE_RATE   " +
                "  FROM RCL_DAILYSALESREPORT A, MAS_PARTY_ROLE C, MAS_PARTY D,mas_dept e,MAS_STORE f\n" +
                " WHERE 1 = 1\n" +
                "   AND C.ID = A.COMPANY_ID\n" +
                "   AND C.PARTY_ID = D.ID " +
                "   AND e.ID = A.DEPT_ID " +
                "   and e.DEPT_TYPE = '000007'" +
                "   AND e.ID = F.DEPT_ID" +
                "   AND f.CLOSING_DAY IS NULL");

        if(param.get("COMPANYID") != null ) stringBuffer.append(" AND A.COMPANY_ID in :COMPANYID ");
        if(param.get("DEPTID") != null )  stringBuffer.append(" AND A.DEPT_ID in :DEPTID ");
        if(param.get("STARTDATE") != null)  stringBuffer.append(" AND A.DATE_OF_STATISTICS >= GET_DATE(:STARTDATE) ");
        if(param.get("ENDDATE") != null)  stringBuffer.append(" AND A.DATE_OF_STATISTICS <= GET_DATE(:ENDDATE) ");
        if (param.get("merge")==null)stringBuffer.append("GROUP BY A.COMPANY_ID, D.NAME,A.DATE_OF_STATISTICS , A.DEPT_ID");
        if (param.get("merge")!=null)stringBuffer.append("GROUP BY A.DATE_OF_STATISTICS");
        param.put("data",stringBuffer.toString());
        JSONArray jsonArray = printSql(param);
        if (!jsonArray.isEmpty())return jsonArray;
        return toJSONArray(find(stringBuffer.toString(), param));
    }
    /**
     * 查询门店日销售数据,汇总
     * @param param
     * @return
     */
    public JSONArray findRCL_DAILYSALESREPORT_ALL_DATE(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("SELECT ");
//        if (param.get("merge")==null)stringBuffer.append("A.COMPANY_ID, A.DEPT_ID,(SELECT NAME FROM MAS_DEPT WHERE ID= A.DEPT_ID) DEPT_NAME, D.NAME COMPANY_NAME, ");
        stringBuffer.append("SUM(SALE_MONEY) SALE_MONEY,\n" +
                "       SUM(PAYMENT_MONEY) PAYMENT_MONEY,\n" +
                "       SUM(ACCOUNTS_RECEIVABLEIN) ACCOUNTS_RECEIVABLEIN,\n" +
                "       SUM(ACCOUNTS_RECEIVABLEOUT) ACCOUNTS_RECEIVABLEOUT,\n" +
                "       SUM(YEARCARD) YEARCARD, SUM(ADVANCE_PAYMENT) ADVANCE_PAYMENT,\n" +
                "       SUM(STORED_VALUE) STORED_VALUE, SUM(BUY_YEARCARD-YEARCARD) BUY_YEARCARD,\n" +
                "       SUM(STORED_VALUE_RECHARGE) STORED_VALUE_RECHARGE,\n" +
                "       SUM(STORED_VALUE_RECHARGE -STORED_VALUE) STORED_VALUE_AMOUNT,\n" +
                "       SUM(ADVANCE_PAYMENT_RECHARGE -ADVANCE_PAYMENT) ADVANCE_AMOUNT,\n" +

                "       SUM(ADVANCE_PAYMENT_RECHARGE) ADVANCE_PAYMENT_RECHARGE,\n" +
                "       SUM(FIXED_LOSS_COLLECTION) FIXED_LOSS_COLLECTION,\n" +
                "       SUM(CAPITAL_INCOME) CAPITAL_INCOME,\n" +
                "       SUM(SERVICE_PERFORMANCE) SERVICE_PERFORMANCE,\n" +
                "       SUM(TIMES_OF_ENTERING_THE_STORE) TIMES_OF_ENTERING_THE_STORE,\n" +
                "       SUM(CAR_WASH) CAR_WASH, SUM(CAR_WASH_TIMES) CAR_WASH_TIMES,\n" +
                "       SUM(COSMETOLOGY) COSMETOLOGY,\n" +
                "       SUM(CROSS_COMPANY_INCOME) CROSS_COMPANY_INCOME,\n" +
                "       SUM(CROSS_COMPANY_PAY)*-1 CROSS_COMPANY_PAY,\n" +
                "       SUM(COSMETOLOGY_TIMES) COSMETOLOGY_TIMES, SUM(MAINTAIN) MAINTAIN,\n" +
                "       SUM(MAINTAIN_TIMES) MAINTAIN_TIMES, SUM(TYRE) TYRE,\n" +
                "       SUM(TYRE_TIMES) TYRE_TIMES, SUM(REPAIR) REPAIR,\n" +
                "       SUM(REPAIR_TIMES) REPAIR_TIMES,\n" +
                "       SUM(SHEET_METAL_SPRAY) SHEET_METAL_SPRAY,\n" +
                "       SUM(SHEET_METAL_SPRAY_TIMES) SHEET_METAL_SPRAY_TIMES,\n" +
                "       SUM(REFIT) REFIT, SUM(REFIT_TIMES) REFIT_TIMES,\n" +
                "       SUM(ROAD_RESCUE) ROAD_RESCUE,\n" +
                "       SUM(ROAD_RESCUE_TIMES) ROAD_RESCUE_TIMES, SUM(BOUTIQUE) BOUTIQUE,\n" +
                "       SUM(ARTICLES_FOR_DAILY_USE) ARTICLES_FOR_DAILY_USE,\n" +
                "       SUM(TECHNICAL_INSPECTION) TECHNICAL_INSPECTION,\n" +
                "       SUM(INFORMATION_SERVICE) INFORMATION_SERVICE,\n" +
                "       SUM(FIXED_LOSS_DIRECT_COMPENSATION) FIXED_LOSS_DIRECT_COMPENSATION,\n" +
                "       SUM(STORED_VALUE_RETAIL) STORED_VALUE_RETAIL,\n" +
                "       SUM(INSURANCE_ORDER_AMT) INSURANCE_ORDER_AMT,\n" +
                "       SUM(FN_COMMISSION) FN_COMMISSION,\n" +
                "       SUM(USER_CAR_FEE) USER_CAR_FEE,\n" +
                "       SUM(FN_SETTLE_EXPENS) FN_SETTLE_EXPENS,\n" +
                "       SUM(USERD_CAR_FINAL) USERD_CAR_FINAL,\n" +
                "       SUM(INSURANCE_AMOUNT) INSURANCE_AMOUNT,\n" +
                "       SUM(OUR_LOSS_COLLECTION) OUR_LOSS_COLLECTION," +
                "       SUM(SELF_FIXED_LOSS)+SUM(INNER_LOSS_COLLECTION_MD)+SUM(INNER_LOSS_COLLECTION) FIXED_LOSS_ALL,\n" +
                "       MAX(FLUSH_DATE) FLUSH_DATE, SUM(INNER_LOSS_COLLECTION_MD) FIXED_LOSS,sum(INNER_LOSS_COLLECTION)INNER_LOSS_COLLECTION,sum(SELF_FIXED_LOSS)SELF_FIXED_LOSS, \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(CAR_WASH)/ SUM(SERVICE_PERFORMANCE))*100,2) END CAR_WASH_RATE,\n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(COSMETOLOGY)/ SUM(SERVICE_PERFORMANCE))*100,2) END COSMETOLOGY_RATE,       \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(MAINTAIN)/ SUM(SERVICE_PERFORMANCE))*100,2) END MAINTAIN_RATE,\n" +

                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(TYRE)/ SUM(SERVICE_PERFORMANCE))*100,2) END TYRE_RATE,       \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(REPAIR)/ SUM(SERVICE_PERFORMANCE))*100,2) END REPAIR_RATE,       \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(SHEET_METAL_SPRAY)/ SUM(SERVICE_PERFORMANCE))*100,2) END SHEET_METAL_SPRAY_RATE,       \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(REFIT)/ SUM(SERVICE_PERFORMANCE))*100,2) END REFIT_RATE,       \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(ROAD_RESCUE)/ SUM(SERVICE_PERFORMANCE))*100,2) END ROAD_RESCUE_RATE,       \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(BOUTIQUE)/ SUM(SERVICE_PERFORMANCE))*100,2) END BOUTIQUE_RATE,       \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(ARTICLES_FOR_DAILY_USE)/ SUM(SERVICE_PERFORMANCE))*100,2) END ARTICLES_FOR_DAILY_USE_RATE,      \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(TECHNICAL_INSPECTION)/ SUM(SERVICE_PERFORMANCE))*100,2) END TECHNICAL_INSPECTION_RATE,       \n" +
                "       CASE WHEN SUM(SERVICE_PERFORMANCE) = 0 THEN 0 ELSE ROUND((SUM(INFORMATION_SERVICE)/ SUM(SERVICE_PERFORMANCE))*100,2) END INFORMATION_SERVICE_RATE   " +
                "  FROM RCL_DAILYSALESREPORT A, MAS_PARTY_ROLE C, MAS_PARTY D,mas_dept e,MAS_STORE f\n" +
                " WHERE 1 = 1\n" +
                "   AND C.ID = A.COMPANY_ID\n" +
                "   AND C.PARTY_ID = D.ID " +
                "   AND e.ID = A.DEPT_ID " +
                "   and e.DEPT_TYPE = '000007'" +
                "   AND e.ID = F.DEPT_ID" +
                "   AND f.CLOSING_DAY IS NULL");

        if(param.get("COMPANYID") != null ) stringBuffer.append(" AND A.COMPANY_ID in :COMPANYID ");
        if(param.get("DEPTID") != null )  stringBuffer.append(" AND A.DEPT_ID in :DEPTID ");
        if(param.get("STARTDATE") != null)  stringBuffer.append(" AND A.DATE_OF_STATISTICS >= GET_DATE(:STARTDATE) ");
        if(param.get("ENDDATE") != null)  stringBuffer.append(" AND A.DATE_OF_STATISTICS < GET_DATE(:ENDDATE)+1 ");
//        if (param.get("merge")==null)stringBuffer.append("GROUP BY A.COMPANY_ID, D.NAME,A.DATE_OF_STATISTICS , A.DEPT_ID");
//        if (param.get("merge")!=null)stringBuffer.append("GROUP BY A.DATE_OF_STATISTICS");
        param.put("data",stringBuffer.toString());
        JSONArray jsonArray = printSql(param);
        if (!jsonArray.isEmpty())return jsonArray;
        return toJSONArray(find(stringBuffer.toString(), param));
    }

    /**
     * 查询应收账款(外)
     * @param param
     * @return
     */
    public JSONArray find_receivableOut(JSONObject param) throws ParseException {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("SELECT NVL(SUM(AMOUNT), 0) AMOUNT, DEPT_ID,DEPT_NAME,\n" +
                "         COMPANY_ID,COMPANY_NAME,CUSTOMER_ID,CUSTOMER_NAME" +
                "        FROM (SELECT B.AMOUNT, C.DEPT_ID,\n" +
                "        (SELECT NAME FROM MAS_DEPT A WHERE A.ID = C.DEPT_ID) DEPT_NAME,\n" +
                "        C.COMPANY_ID,\n" +
                "        (SELECT C.NAME\n" +
                "        FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "        WHERE C.ID = D.PARTY_ID\n" +
                "        AND D.ID = C.COMPANY_ID) COMPANY_NAME,\n" +
                "        B.CUSTOMER_ID,\n" +
                "        (SELECT C.NAME\n" +
                "        FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "        WHERE C.ID = D.PARTY_ID\n" +
                "        AND D.ID = B.CUSTOMER_ID) CUSTOMER_NAME,\n" +
                "        C.DATE_APPROVE\n" +
                "        FROM RETAIL_ORDER_PAYMENT_METHOD B, RETAIL_ORDER C\n" +
                "        WHERE B.RETAIL_ORDER_ID = C.ID\n" +
//                "        AND C.STATE IN ('done', 'confirmed')\n" +
                "        AND C.CLAIM_TYPE IS NULL\n" +
                "        AND B.PAYMENT_METHOD_TYPE IN ('09')\n" +
                "       and b.CUSTOMER_ID not in ('C0005998', '02532856', '02533043')" +
                "        and (( (not EXISTS(SELECT 1\n" +
                "                                                                FROM MAS_PARTY_ROLE R\n" +
                "                                                                WHERE R.PARTY_ID = (SELECT MPR.PARTY_ID\n" +
                "                                                                                    FROM MAS_PARTY_ROLE MPR\n" +
                "                                                                                    WHERE MPR.ID = B.CUSTOMER_ID)\n" +
                "                                                                  and R.ROLE_ID = 'Company') or\n" +
                "                                                     ((SELECT t.Party_Id FROM MAS_PARTY_ROLE t WHERE T.ID = C.COMPANY_ID)\n" +
                "                                                         =\n" +
                "                                                      (SELECT t.Party_Id FROM MAS_PARTY_ROLE t WHERE T.ID = B.CUSTOMER_ID)) or\n" +
                "                                                     C.ORDER_TYPE <>\n" +
                "                                                     'replacement'and B.CUSTOMER_ID not IN ('K1001', 'K1003')) ) or\n" +
                "             (B.CUSTOMER_ID IN ('K1001', 'K1003') AND C.ORDER_TYPE = 'replacement' and\n" +
                "              ((SELECT t.Party_Id FROM MAS_PARTY_ROLE t WHERE T.ID = C.COMPANY_ID) = (SELECT t.Party_Id\n" +
                "                                                                                      FROM MAS_PARTY_ROLE t\n" +
                "                                                                                      WHERE T.ID = B.CUSTOMER_ID) or\n" +
                "               (not EXISTS(SELECT 1\n" +
                "                           FROM MAS_PARTY_ROLE R\n" +
                "                           WHERE R.PARTY_ID =\n" +
                "                                 (SELECT MPR.PARTY_ID FROM MAS_PARTY_ROLE MPR WHERE MPR.ID = B.CUSTOMER_ID)\n" +
                "                             and R.ROLE_ID = 'Company')))))\n\n" +
                "        UNION ALL\n" +
                "        SELECT B.AMOUNT,C.DEPT_ID,\n" +
                "        (SELECT NAME FROM MAS_DEPT A WHERE A.ID = C.DEPT_ID) DEPT_NAME,\n" +
                "        C.COMPANY_ID,\n" +
                "        (SELECT C.NAME\n" +
                "        FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "        WHERE C.ID = D.PARTY_ID\n" +
                "        AND D.ID = C.COMPANY_ID) COMPANY_NAME,\n" +
                "        B.CUSTOMER_ID,\n" +
                "        (SELECT C.NAME\n" +
                "        FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "        WHERE C.ID = D.PARTY_ID\n" +
                "        AND D.ID = B.CUSTOMER_ID) CUSTOMER_NAME,\n" +
                "        C.DATE_APPROVE\n" +
                "        FROM ADVANCE_PAYMENT_METHOD B, ADVANCE_PAYMENT C\n" +
                "        WHERE B.payment_id = C.ID\n" +
                "        AND C.STATE = 'approve'\n" +
                "        AND B.PAYMENT_METHOD_TYPE IN ('09')\n" +
                "        AND B.CUSTOMER_ID <> 'C0005998'        AND (((SELECT t.Party_Id\n" +
                "                       FROM MAS_PARTY_ROLE t\n" +
                "                       WHERE T.ID = C.COMPANY_ID) =\n" +
                "                      (SELECT t.Party_Id\n" +
                "                       FROM MAS_PARTY_ROLE t\n" +
                "                       WHERE T.ID = B.CUSTOMER_ID)) OR NOT EXISTS(SELECT 1\n" +
                "                                                                  FROM MAS_PARTY_ROLE R\n" +
                "                                                                  WHERE R.PARTY_ID = (SELECT MPR.PARTY_ID\n" +
                "                                                                                      FROM MAS_PARTY_ROLE MPR\n" +
                "                                                                                      WHERE MPR.ID = B.CUSTOMER_ID)\n" +
                "                                                                    and R.ROLE_ID = 'Company'\n" +
                "                  )))\n" +
                "        WHERE 1 = 1 ");
        JSONArray DATE_OF_STATISTICS = param.getJSONArray("DATE_OF_STATISTICS");
        if (DATE_OF_STATISTICS!=null) {
            String DATE_OF_STATISTICS_S = DATE_OF_STATISTICS.getString(0);
            Date DATE_OF_STATISTICS_STATE = StringToDate(DATE_OF_STATISTICS_S,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_STATE_S = DateUtil.format(DATE_OF_STATISTICS_STATE,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_STATE",DATE_OF_STATISTICS_STATE_S);
            String DATE_OF_STATISTICS_E = DATE_OF_STATISTICS.getString(1);
            Date DATE_OF_STATISTICS_END = StringToDate(DATE_OF_STATISTICS_E,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_END_S = DateUtil.format(DATE_OF_STATISTICS_END,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_END",DATE_OF_STATISTICS_END_S);
        }
        DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        if(param.get("COMPANY_ID") != null ) stringBuffer.append(" AND COMPANY_ID = :COMPANY_ID ");
        if(param.get("DEPT_ID") != null )  stringBuffer.append(" AND DEPT_ID = :DEPT_ID ");
        if(param.get("DATE_OF_STATISTICS_STATE") != null)  stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd')  ");
        if(param.get("DATE_OF_STATISTICS_END") != null)  stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
        if(param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_STATE") != null)  stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd')  ");
        if(param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_END") != null)  stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
        if(param.get("DATE_APPROVE") != null) {
            try {
                Date date = formatter.parse(param.getString("DATE_APPROVE"));
                stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_APPROVE,'yyyy-MM-dd') ");
                stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_APPROVE,'yyyy-MM-dd') +1");
            } catch (Exception e) {
                Date start = formatter.parse(param.getString("DATE_OF_STATISTICS_STATE"));
                Date end = formatter.parse(param.getString("DATE_OF_STATISTICS_END"));
                LocalDate localDateStart = DateUtil.UDateToLocalDate(start);
                LocalDate localDateEnd = DateUtil.UDateToLocalDate(end);
                if (localDateStart.getMonthValue() == localDateEnd.getMonthValue()) {
                    stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd') ");
                    stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
                } else {
                    formatter = new SimpleDateFormat("yyyy-MM");
                    Date dateApprove = formatter.parse(param.getString("DATE_APPROVE"));
                    LocalDate loDateApprove = DateUtil.UDateToLocalDate(dateApprove);
                    if (localDateStart.getMonthValue() == loDateApprove.getMonthValue()){
                        //获取月末
                        LocalDate monthOfLastDate = loDateApprove.with(TemporalAdjusters.lastDayOfMonth());
                        String SmonthOfLastDate = monthOfLastDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                        param.put("SmonthOfLastDate",SmonthOfLastDate);
                        stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd') ");
                        stringBuffer.append(" AND DATE_APPROVE < to_date(:SmonthOfLastDate,'yyyy-MM-dd') + 1");
                    } else {
                        //获取月头
                        LocalDate monthOfFirstDate = loDateApprove.with(TemporalAdjusters.firstDayOfMonth());
                        LocalDate monthOfLastDate = loDateApprove.with(TemporalAdjusters.lastDayOfMonth());
                        String SmonthOfFirstDate = monthOfFirstDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                        String monthOfLastDateStr = monthOfLastDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                        param.put("SmonthOfFirstDate",SmonthOfFirstDate);
                        param.put("SmonthOfLastDate",monthOfLastDateStr);
                        stringBuffer.append(" AND DATE_APPROVE >= to_date(:SmonthOfFirstDate,'yyyy-MM-dd') ");
                        stringBuffer.append(" AND DATE_APPROVE < to_date(:SmonthOfLastDate,'yyyy-MM-dd') + 1");
                    }
                }

            }
        }
        stringBuffer.append(" GROUP BY DEPT_ID, DEPT_NAME,COMPANY_ID,COMPANY_NAME,CUSTOMER_ID,CUSTOMER_NAME ");
        return toJSONArray(find(stringBuffer.toString(), param));
    }

    public String get_param(JSONObject param ) throws Exception {
        StringBuffer stringBuffer = new StringBuffer();
        JSONArray DATE_OF_STATISTICS = param.getJSONArray("DATE_OF_STATISTICS");
        try {
            JSONArray DATE_APPROVE_S = param.getJSONArray("DATE_APPROVE");
            if (DATE_APPROVE_S.size() == 2) {
                DATE_OF_STATISTICS = DATE_APPROVE_S;
                param.put("DATE_APPROVE", null);
            }
        } catch (Exception e) {

        }
        if (DATE_OF_STATISTICS != null) {
            String DATE_OF_STATISTICS_S = DATE_OF_STATISTICS.getString(0);
            Date DATE_OF_STATISTICS_STATE = StringToDate(DATE_OF_STATISTICS_S, DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_STATE_S = DateUtil.format(DATE_OF_STATISTICS_STATE, DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_STATE", DATE_OF_STATISTICS_STATE_S);
            String DATE_OF_STATISTICS_E = DATE_OF_STATISTICS.getString(1);
            Date DATE_OF_STATISTICS_END = StringToDate(DATE_OF_STATISTICS_E, DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_END_S = DateUtil.format(DATE_OF_STATISTICS_END, DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_END", DATE_OF_STATISTICS_END_S);
        }
        DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        if (param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_STATE") != null)
            stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd')  ");
        if (param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_END") != null)
            stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1 ");
        if (param.get("COMPANY_ID") != null) stringBuffer.append(" AND COMPANY_ID = :COMPANY_ID ");
        if (param.get("DEPT_ID") != null) stringBuffer.append(" AND DEPT_ID = :DEPT_ID ");
        if (param.get("PAYMENT_METHOD_TYPE") != null)
            stringBuffer.append(" AND PAYMENT_METHOD_TYPE  = :PAYMENT_METHOD_TYPE ");
        if (param.get("DATE_APPROVE") != null) {
            try {
                Date date = formatter.parse(param.getString("DATE_APPROVE"));
                stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_APPROVE,'yyyy-MM-dd') ");
                stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_APPROVE,'yyyy-MM-dd') +1");
            } catch (Exception e) {
                Date start = formatter.parse(param.getString("DATE_OF_STATISTICS_STATE"));
                Date end = formatter.parse(param.getString("DATE_OF_STATISTICS_END"));
                LocalDate localDateStart = DateUtil.UDateToLocalDate(start);
                LocalDate localDateEnd = DateUtil.UDateToLocalDate(end);
                if (localDateStart.getMonthValue() == localDateEnd.getMonthValue()) {
                    stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd') ");
                    stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
                } else {
                    formatter = new SimpleDateFormat("yyyy-MM");
                    Date dateApprove = formatter.parse(param.getString("DATE_APPROVE"));
                    LocalDate loDateApprove = DateUtil.UDateToLocalDate(dateApprove);
                    if (localDateStart.getMonthValue() == loDateApprove.getMonthValue()) {
                        //获取月末
                        LocalDate monthOfLastDate = loDateApprove.with(TemporalAdjusters.lastDayOfMonth());
                        String SmonthOfLastDate = monthOfLastDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                        param.put("SmonthOfLastDate", SmonthOfLastDate);
                        stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd') ");
                        stringBuffer.append(" AND DATE_APPROVE < to_date(:SmonthOfLastDate,'yyyy-MM-dd') + 1");
                    } else {
                        //获取月头
                        LocalDate monthOfFirstDate = loDateApprove.with(TemporalAdjusters.firstDayOfMonth());
                        String SmonthOfFirstDate = monthOfFirstDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                        param.put("SmonthOfFirstDate", SmonthOfFirstDate);
                        stringBuffer.append(" AND DATE_APPROVE >= to_date(:SmonthOfFirstDate,'yyyy-MM-dd') ");
                        stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
                    }
                }

            }
        }
        return stringBuffer.toString();
    }

    /**
     * 查询应收账款(外)明细
     * @param param
     * @return
     */
    public JSONArray find_receivableOutMx(JSONObject param) throws ParseException {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("SELECT NVL(SUM(AMOUNT), 0) AMOUNT, DEPT_ID,DEPT_NAME,\n" +
                "         COMPANY_ID,COMPANY_NAME,CUSTOMER_ID,CUSTOMER_NAME," +
                "        TO_CHAR(DATE_APPROVE,'yyyy-MM-dd') as DATE_OF_STATISTICS\n" +
                "        FROM (SELECT B.AMOUNT, C.DEPT_ID,\n" +
                "        (SELECT NAME FROM MAS_DEPT A WHERE A.ID = C.DEPT_ID) DEPT_NAME,\n" +
                "        C.COMPANY_ID,\n" +
                "        (SELECT C.NAME\n" +
                "        FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "        WHERE C.ID = D.PARTY_ID\n" +
                "        AND D.ID = C.COMPANY_ID) COMPANY_NAME,\n" +
                "        B.CUSTOMER_ID,\n" +
                "        (SELECT C.NAME\n" +
                "        FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "        WHERE C.ID = D.PARTY_ID\n" +
                "        AND D.ID = B.CUSTOMER_ID) CUSTOMER_NAME,\n" +
                "        C.DATE_APPROVE\n" +
                "        FROM RETAIL_ORDER_PAYMENT_METHOD B, RETAIL_ORDER C\n" +
                "        WHERE B.RETAIL_ORDER_ID = C.ID\n" +
//                "        AND C.STATE IN ('done', 'confirmed')\n" +
                "        AND C.CLAIM_TYPE IS NULL\n" +
                "        AND B.PAYMENT_METHOD_TYPE IN ('09')\n" +
                "        and b.CUSTOMER_ID not in ('C0005998', '02532856', '02533043')" +
                "        and (( (not EXISTS(SELECT 1\n" +
                "                                                                FROM MAS_PARTY_ROLE R\n" +
                "                                                                WHERE R.PARTY_ID = (SELECT MPR.PARTY_ID\n" +
                "                                                                                    FROM MAS_PARTY_ROLE MPR\n" +
                "                                                                                    WHERE MPR.ID = B.CUSTOMER_ID)\n" +
                "                                                                  and R.ROLE_ID = 'Company') or\n" +
                "                                                     ((SELECT t.Party_Id FROM MAS_PARTY_ROLE t WHERE T.ID = C.COMPANY_ID)\n" +
                "                                                         =\n" +
                "                                                      (SELECT t.Party_Id FROM MAS_PARTY_ROLE t WHERE T.ID = B.CUSTOMER_ID)) or\n" +
                "                                                     C.ORDER_TYPE <>\n" +
                "                                                     'replacement'and B.CUSTOMER_ID not IN ('K1001', 'K1003')) ) or\n" +
                "             (B.CUSTOMER_ID IN ('K1001', 'K1003') AND C.ORDER_TYPE = 'replacement' and\n" +
                "              ((SELECT t.Party_Id FROM MAS_PARTY_ROLE t WHERE T.ID = C.COMPANY_ID) = (SELECT t.Party_Id\n" +
                "                                                                                      FROM MAS_PARTY_ROLE t\n" +
                "                                                                                      WHERE T.ID = B.CUSTOMER_ID) or\n" +
                "               (not EXISTS(SELECT 1\n" +
                "                           FROM MAS_PARTY_ROLE R\n" +
                "                           WHERE R.PARTY_ID =\n" +
                "                                 (SELECT MPR.PARTY_ID FROM MAS_PARTY_ROLE MPR WHERE MPR.ID = B.CUSTOMER_ID)\n" +
                "                             and R.ROLE_ID = 'Company')))))\n" +
                "        UNION ALL\n" +
                "        SELECT B.AMOUNT,C.DEPT_ID,\n" +
                "        (SELECT NAME FROM MAS_DEPT A WHERE A.ID = C.DEPT_ID) DEPT_NAME,\n" +
                "        C.COMPANY_ID,\n" +
                "        (SELECT C.NAME\n" +
                "        FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "        WHERE C.ID = D.PARTY_ID\n" +
                "        AND D.ID = C.COMPANY_ID) COMPANY_NAME,\n" +
                "        B.CUSTOMER_ID,\n" +
                "        (SELECT C.NAME\n" +
                "        FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "        WHERE C.ID = D.PARTY_ID\n" +
                "        AND D.ID = B.CUSTOMER_ID) CUSTOMER_NAME,\n" +
                "        C.DATE_APPROVE\n" +
                "        FROM ADVANCE_PAYMENT_METHOD B, ADVANCE_PAYMENT C\n" +
                "        WHERE B.payment_id = C.ID\n" +
                "        AND C.STATE = 'approve'\n" +
                "        AND B.PAYMENT_METHOD_TYPE IN ('09')\n" +
                "        AND  B.CUSTOMER_ID <> 'C0005998'        AND (((SELECT t.Party_Id\n" +
                "                       FROM MAS_PARTY_ROLE t\n" +
                "                       WHERE T.ID = C.COMPANY_ID) =\n" +
                "                      (SELECT t.Party_Id\n" +
                "                       FROM MAS_PARTY_ROLE t\n" +
                "                       WHERE T.ID = B.CUSTOMER_ID)) OR NOT EXISTS(SELECT 1\n" +
                "                                                                  FROM MAS_PARTY_ROLE R\n" +
                "                                                                  WHERE R.PARTY_ID = (SELECT MPR.PARTY_ID\n" +
                "                                                                                      FROM MAS_PARTY_ROLE MPR\n" +
                "                                                                                      WHERE MPR.ID = B.CUSTOMER_ID)\n" +
                "                                                                    and R.ROLE_ID = 'Company'\n" +
                "                  )))\n" +
                "        WHERE 1 = 1 ");
        JSONArray DATE_OF_STATISTICS = param.getJSONArray("DATE_OF_STATISTICS");
        try {
            JSONArray DATE_APPROVE_S = param.getJSONArray("DATE_APPROVE");
            if (DATE_APPROVE_S.size() == 2) {
                DATE_OF_STATISTICS = DATE_APPROVE_S;
                param.put("DATE_APPROVE", null);
            }
        } catch (Exception e) {

        }
        if (DATE_OF_STATISTICS!=null) {
            String DATE_OF_STATISTICS_S = DATE_OF_STATISTICS.getString(0);
            Date DATE_OF_STATISTICS_STATE = StringToDate(DATE_OF_STATISTICS_S,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_STATE_S = DateUtil.format(DATE_OF_STATISTICS_STATE,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_STATE",DATE_OF_STATISTICS_STATE_S);
            String DATE_OF_STATISTICS_E = DATE_OF_STATISTICS.getString(1);
            Date DATE_OF_STATISTICS_END = StringToDate(DATE_OF_STATISTICS_E,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_END_S = DateUtil.format(DATE_OF_STATISTICS_END,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_END",DATE_OF_STATISTICS_END_S);
        }
        DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        if(param.get("COMPANY_ID") != null ) stringBuffer.append(" AND COMPANY_ID = :COMPANY_ID ");
        if(param.get("DEPT_ID") != null )  stringBuffer.append(" AND DEPT_ID = :DEPT_ID ");
        if(param.get("CUSTOMER_ID") != null ) stringBuffer.append(" AND CUSTOMER_ID = :CUSTOMER_ID ");

        if(param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_STATE") != null)  stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd')  ");
        if(param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_END") != null)  stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
        if(param.get("DATE_APPROVE") != null) {
            try {
                Date date = formatter.parse(param.getString("DATE_APPROVE"));
                stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_APPROVE,'yyyy-MM-dd') ");
                stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_APPROVE,'yyyy-MM-dd') +1");
            } catch (Exception e) {
                if (!param.containsKey("DATE_OF_STATISTICS_STATE")){
                    formatter = new SimpleDateFormat("yyyy-MM");
                    Date dateApprove = formatter.parse(param.getString("DATE_APPROVE"));
                    LocalDate loDateApprove = DateUtil.UDateToLocalDate(dateApprove);
                    LocalDate monthOfFirstDate = loDateApprove.with(TemporalAdjusters.firstDayOfMonth());
                    LocalDate monthOfLastDate = loDateApprove.with(TemporalAdjusters.lastDayOfMonth());
                    String SmonthOfFirstDate = monthOfFirstDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                    String monthOfLastDateStr = monthOfLastDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                    param.put("SmonthOfFirstDate",SmonthOfFirstDate);
                    param.put("DATE_OF_STATISTICS_END",monthOfLastDateStr);
                    stringBuffer.append(" AND DATE_APPROVE >= to_date(:SmonthOfFirstDate,'yyyy-MM-dd') ");
                    stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");

                }else {
                    Date start = formatter.parse(param.getString("DATE_OF_STATISTICS_STATE"));
                    Date end = formatter.parse(param.getString("DATE_OF_STATISTICS_END"));
                    LocalDate localDateStart = DateUtil.UDateToLocalDate(start);
                    LocalDate localDateEnd = DateUtil.UDateToLocalDate(end);
                    if (localDateStart.getMonthValue() == localDateEnd.getMonthValue()) {
                        stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd') ");
                        stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
                    } else {
                        formatter = new SimpleDateFormat("yyyy-MM");
                        Date dateApprove = formatter.parse(param.getString("DATE_APPROVE"));
                        LocalDate loDateApprove = DateUtil.UDateToLocalDate(dateApprove);
                        if (localDateStart.getMonthValue() == loDateApprove.getMonthValue()){
                            //获取月末
                            LocalDate monthOfLastDate = loDateApprove.with(TemporalAdjusters.lastDayOfMonth());
                            String SmonthOfLastDate = monthOfLastDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                            param.put("SmonthOfLastDate",SmonthOfLastDate);
                            stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd') ");
                            stringBuffer.append(" AND DATE_APPROVE < to_date(:SmonthOfLastDate,'yyyy-MM-dd') + 1");
                        } else {
                            //获取月头
                            LocalDate monthOfFirstDate = loDateApprove.with(TemporalAdjusters.firstDayOfMonth());
                            LocalDate monthOfLastDate = loDateApprove.with(TemporalAdjusters.lastDayOfMonth());
                            String SmonthOfFirstDate = monthOfFirstDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                            String monthOfLastDateStr = monthOfLastDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                            param.put("SmonthOfFirstDate",SmonthOfFirstDate);
                            param.put("DATE_OF_STATISTICS_END",monthOfLastDateStr);
                            stringBuffer.append(" AND DATE_APPROVE >= to_date(:SmonthOfFirstDate,'yyyy-MM-dd') ");
                            stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
                        }
                    }
                }
            }
        }
        stringBuffer.append(" GROUP BY DEPT_ID, DEPT_NAME,COMPANY_ID,COMPANY_NAME,CUSTOMER_ID,CUSTOMER_NAME,TO_CHAR(DATE_APPROVE, 'yyyy-MM-dd') ");
        stringBuffer.append(" order BY TO_CHAR(DATE_APPROVE, 'yyyy-MM-dd')");

        return toJSONArray(find(stringBuffer.toString(), param));
    }

    /**
     * 查询应收账款(外)明细
     * @param param
     * @return
     */
    public JSONArray find_receivableOutMxGdj(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append(" SELECT NVL(SUM(AMOUNT), 0) AMOUNT,\n" +
                "       DEPT_ID,\n" +
                "       DEPT_NAME,\n" +
                "       COMPANY_ID,\n" +
                "       COMPANY_NAME,\n" +
                "       CUSTOMER_ID,\n" +
                "       CUSTOMER_NAME,\n" +
                "       TO_CHAR(DATE_APPROVE,'yyyy-MM-dd') AS DATE_OF_STATISTICS,\n" +
                "       BUSINESS_ID AS ID, \n" +
                "       FORM_ID,API,"  +
                "       INNER_BUSINESS_TYPE," +
                "       SOURCE_ID,\n" +
                "       SOURCE_BUSINESS_TYPE,\n" +
                "       CAR_LICENSE,\n" +
                "       (GET_ENCRYPT_MOBILE(MOBILE)) AS MOBILE ,\n" +
                "       PAYMENT_METHOD_TYPE_NAME,\n" +
                "       PAYMENT_METHOD_TYPE\n" +
                "  FROM (SELECT B.AMOUNT,\n" +
                "               C.ID BUSINESS_ID," +
                "               'form/retail.order' FORM_ID,'retail.order.findOne' API,"+
                "              '零售单' AS INNER_BUSINESS_TYPE," +
                "               C.DEPT_ID,\n" +
                "               (SELECT NAME FROM MAS_DEPT A WHERE A.ID = C.DEPT_ID) DEPT_NAME,\n" +
                "               C.COMPANY_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = C.COMPANY_ID) COMPANY_NAME,\n" +
                "               B.CUSTOMER_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = B.CUSTOMER_ID) CUSTOMER_NAME,\n" +
                "               C.DATE_APPROVE,\n" +
                "               C.SOURCE SOURCE_ID,\n" +
                "               C.SOURCE_BUSINESS_TYPE,\n" +
                "               (SELECT T.CAR_LICENSE\n" +
                "                  FROM FN_CUSTOMER_EXPAND T\n" +
                "                 WHERE T.ID = C.CUSTOMER_EXPAND_ID) CAR_LICENSE,\n" +
                "               (SELECT (GET_ENCRYPT_MOBILE(T.MOBILE)) AS MOBILE\n" +
                "                  FROM FN_CUSTOMER_EXPAND T\n" +
                "                 WHERE T.ID = C.CUSTOMER_EXPAND_ID) MOBILE,\n" +
                "               (SELECT t.name\n" +
                "                  FROM mas_payment_method t\n" +
                "                 WHERE t.id = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_TYPE_NAME,\n" +
                "               B.PAYMENT_METHOD_TYPE\n" +
                "          FROM RETAIL_ORDER_PAYMENT_METHOD B, RETAIL_ORDER C\n" +
                "         WHERE B.RETAIL_ORDER_ID = C.ID\n" +
//                "           AND C.STATE IN ('done', 'confirmed')\n" +
                "           AND C.CLAIM_TYPE IS NULL\n" +
                "           AND B.PAYMENT_METHOD_TYPE IN ('09')\n" +
                "           and b.CUSTOMER_ID not in ('C0005998', '02532856', '02533043')" +
                "           and (( (not EXISTS(SELECT 1\n" +
                "                                                                FROM MAS_PARTY_ROLE R\n" +
                "                                                                WHERE R.PARTY_ID = (SELECT MPR.PARTY_ID\n" +
                "                                                                                    FROM MAS_PARTY_ROLE MPR\n" +
                "                                                                                    WHERE MPR.ID = B.CUSTOMER_ID)\n" +
                "                                                                  and R.ROLE_ID = 'Company') or\n" +
                "                                                     ((SELECT t.Party_Id FROM MAS_PARTY_ROLE t WHERE T.ID = C.COMPANY_ID)\n" +
                "                                                         =\n" +
                "                                                      (SELECT t.Party_Id FROM MAS_PARTY_ROLE t WHERE T.ID = B.CUSTOMER_ID)) or\n" +
                "                                                     C.ORDER_TYPE <>\n" +
                "                                                     'replacement'and B.CUSTOMER_ID not IN ('K1001', 'K1003')) ) or\n" +
                "             (B.CUSTOMER_ID IN ('K1001', 'K1003') AND C.ORDER_TYPE = 'replacement' and\n" +
                "              ((SELECT t.Party_Id FROM MAS_PARTY_ROLE t WHERE T.ID = C.COMPANY_ID) = (SELECT t.Party_Id\n" +
                "                                                                                      FROM MAS_PARTY_ROLE t\n" +
                "                                                                                      WHERE T.ID = B.CUSTOMER_ID) or\n" +
                "               (not EXISTS(SELECT 1\n" +
                "                           FROM MAS_PARTY_ROLE R\n" +
                "                           WHERE R.PARTY_ID =\n" +
                "                                 (SELECT MPR.PARTY_ID FROM MAS_PARTY_ROLE MPR WHERE MPR.ID = B.CUSTOMER_ID)\n" +
                "                             and R.ROLE_ID = 'Company')))))\n" +
                "        UNION ALL\n" +
                "        SELECT B.AMOUNT," +
                "               C.ID BUSINESS_ID, " +
                "               'form/advance.payment' FORM_ID,'advance.payment.findOne' API,"+
                "               '订金单' AS INNER_BUSINESS_TYPE,\n" +
                "               C.DEPT_ID,\n" +
                "               (SELECT NAME FROM MAS_DEPT A WHERE A.ID = C.DEPT_ID) DEPT_NAME,\n" +
                "               C.COMPANY_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = C.COMPANY_ID) COMPANY_NAME,\n" +
                "               B.CUSTOMER_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = B.CUSTOMER_ID) CUSTOMER_NAME,\n" +
                "               C.DATE_APPROVE,\n" +
                "               c.id source_id,\n" +
                "               c.INNER_BUSINESS_TYPE source_business_type,\n" +
                "               (SELECT t.car_license\n" +
                "                  FROM FN_CUSTOMER_EXPAND t\n" +
                "                 WHERE t.id = c.customer_expand_id) car_license,\n" +
                "               (SELECT (GET_ENCRYPT_MOBILE(t.mobile)) AS mobile\n" +
                "                  FROM FN_CUSTOMER_EXPAND t\n" +
                "                 WHERE t.id = c.customer_expand_id) mobile,\n" +
                "               (SELECT t.name\n" +
                "                  FROM mas_payment_method t\n" +
                "                 WHERE t.id = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_TYPE_NAME,\n" +
                "               B.PAYMENT_METHOD_TYPE\n" +
                "          FROM ADVANCE_PAYMENT_METHOD B, ADVANCE_PAYMENT C\n" +
                "         WHERE B.payment_id = C.ID\n" +
                "           AND C.STATE = 'approve'\n" +
                "           AND B.PAYMENT_METHOD_TYPE IN ('09')\n" +
                "           AND B.CUSTOMER_ID <> 'C0005998'        AND (((SELECT t.Party_Id\n" +
                "                       FROM MAS_PARTY_ROLE t\n" +
                "                       WHERE T.ID = C.COMPANY_ID) =\n" +
                "                      (SELECT t.Party_Id\n" +
                "                       FROM MAS_PARTY_ROLE t\n" +
                "                       WHERE T.ID = B.CUSTOMER_ID)) OR NOT EXISTS(SELECT 1\n" +
                "                                                                  FROM MAS_PARTY_ROLE R\n" +
                "                                                                  WHERE R.PARTY_ID = (SELECT MPR.PARTY_ID\n" +
                "                                                                                      FROM MAS_PARTY_ROLE MPR\n" +
                "                                                                                      WHERE MPR.ID = B.CUSTOMER_ID)\n" +
                "                                                                    and R.ROLE_ID = 'Company'\n" +
                "                  )))\n" +
                " where 1 = 1");
        JSONArray DATE_OF_STATISTICS = param.getJSONArray("DATE_OF_STATISTICS");
        if (DATE_OF_STATISTICS!=null) {
            String DATE_OF_STATISTICS_S = DATE_OF_STATISTICS.getString(0);
            Date DATE_OF_STATISTICS_STATE = StringToDate(DATE_OF_STATISTICS_S,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_STATE_S = DateUtil.format(DATE_OF_STATISTICS_STATE,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_STATE",DATE_OF_STATISTICS_STATE_S);
            String DATE_OF_STATISTICS_E = DATE_OF_STATISTICS.getString(1);
            Date DATE_OF_STATISTICS_END = StringToDate(DATE_OF_STATISTICS_E,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_END_S = DateUtil.format(DATE_OF_STATISTICS_END,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_END",DATE_OF_STATISTICS_END_S);
        }

        if(param.get("COMPANY_ID") != null ) stringBuffer.append(" AND COMPANY_ID = :COMPANY_ID ");
        if(param.get("CUSTOMER_ID") != null ) stringBuffer.append(" AND CUSTOMER_ID = :CUSTOMER_ID ");
        if(param.get("DEPT_ID") != null )  stringBuffer.append(" AND DEPT_ID = :DEPT_ID ");
        if(param.get("DATE_APPROVE") != null)  stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_APPROVE,'yyyy-MM-dd') ");
        if(param.get("DATE_APPROVE") != null)  stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_APPROVE,'yyyy-MM-dd') + 1");
        if(param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_STATE") != null)  stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd')  ");
        if(param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_END") != null)  stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
        stringBuffer.append(" GROUP BY DEPT_ID,\n" +
                "          DEPT_NAME,\n" +
                "          COMPANY_ID,\n" +
                "          COMPANY_NAME,\n" +
                "          CUSTOMER_ID,\n" +
                "          CUSTOMER_NAME,\n" +
                "          TO_CHAR(DATE_APPROVE, 'yyyy-MM-dd'),\n" +
                "          BUSINESS_ID, \n" +
                "          FORM_ID,API," +
                "          INNER_BUSINESS_TYPE," +
                "          source_id,\n" +
                "          source_business_type,\n" +
                "          car_license,\n" +
                "          mobile,\n" +
                "          PAYMENT_METHOD_TYPE_NAME,\n" +
                "          PAYMENT_METHOD_TYPE ");

        return toJSONArray(find(stringBuffer.toString(), param));
    }

    /**
     * 查询应收账款(内)明细
     * @param param
     * @return
     */
    public JSONArray find_receivableIn(JSONObject param) throws ParseException {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("SELECT NVL(SUM(AMOUNT), 0) AMOUNT,\n" +
                "       DEPT_ID,\n" +
                "       DEPT_NAME,\n" +
                "       COMPANY_ID,\n" +
                "       COMPANY_NAME,\n" +
                "       ACCOUNTTYPE\n" +
                "  FROM (SELECT B.AMOUNT,\n" +
                "               C.DEPT_ID,\n" +
                "               (SELECT NAME FROM MAS_DEPT A WHERE A.ID = C.DEPT_ID) DEPT_NAME,\n" +
                "               C.COMPANY_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = C.COMPANY_ID) COMPANY_NAME,\n" +
                "               '集群引流' accountType,    \n" +
                "               C.DATE_APPROVE\n" +
                "          FROM RETAIL_ORDER_PAYMENT_METHOD B, RETAIL_ORDER C \n" +
                "         WHERE B.RETAIL_ORDER_ID = C.ID\n" +
//                "           AND C.STATE IN ('done', 'confirmed')\n" +
                "           AND B.PAYMENT_METHOD_TYPE IN ('09')\n" +
                "           AND C.CLAIM_TYPE IS NULL\n" +
                "           AND (\n" +
                "                    (B.CUSTOMER_ID IN ('K1001','K1003','C0005998','02532856','02533043') AND C.ORDER_TYPE<>'replacement' " +
                "                            AND ((SELECT t.Party_Id\n" +
                "                          FROM MAS_PARTY_ROLE t\n" +
                "                          WHERE T.ID = C.COMPANY_ID) !=\n" +
                "                         (SELECT t.Party_Id\n" +
                "                          FROM MAS_PARTY_ROLE t\n" +
                "                          WHERE T.ID = B.CUSTOMER_ID)) )\n" +
                "                ) \n" +
                "           AND  NOT EXISTS (SELECT 1 FROM RETAIL_ORDER_ITEM E " +
                "                       WHERE E.PRODUCT_ID IN ('0002935068','0002902247','0002935072','0003020877','0002902250') " +
                "                   AND E.SALE_ID = C.ID) " +
                "        UNION ALL\n" +
                "      SELECT B.AMOUNT,\n" +
                "             C.DEPT_ID,\n" +
                "             (SELECT NAME FROM MAS_DEPT A WHERE A.ID = C.DEPT_ID) DEPT_NAME,\n" +
                "             C.COMPANY_ID,\n" +
                "             (SELECT C.NAME\n" +
                "              FROM MAS_PARTY C,\n" +
                "                   MAS_PARTY_ROLE D\n" +
                "              WHERE C.ID = D.PARTY_ID\n" +
                "                AND D.ID = C.COMPANY_ID)                          COMPANY_NAME,\n" +
                "             '兑换券置换'                                              accountType,\n" +
                "             C.DATE_APPROVE\n" +
                "      FROM RETAIL_ORDER_PAYMENT_METHOD B,\n" +
                "           RETAIL_ORDER C\n" +
                "      WHERE B.RETAIL_ORDER_ID = C.ID\n" +
                "        AND B.PAYMENT_METHOD_TYPE IN ('09')\n" +
                "        AND C.CLAIM_TYPE\n" +
                "          IS NULL\n" +
                "\n" +
                "        and (EXISTS(SELECT 1\n" +
                "                    FROM MAS_PARTY_ROLE R\n" +
                "                    WHERE R.PARTY_ID = (SELECT MPR.PARTY_ID FROM MAS_PARTY_ROLE MPR WHERE MPR.ID = B.CUSTOMER_ID)\n" +
                "                      and R.ROLE_ID = 'Company')\n" +
                "          AND C.ORDER_TYPE = 'replacement' AND ((SELECT t.Party_Id\n" +
                "                                                 FROM MAS_PARTY_ROLE t\n" +
                "                                                 WHERE T.ID = C.COMPANY_ID) !=\n" +
                "                                                (SELECT t.Party_Id FROM MAS_PARTY_ROLE t WHERE T.ID = B.CUSTOMER_ID))\n" +
                "          )\n" +
                "\n" +
                "      UNION ALL\n" +
                "        SELECT B.AMOUNT,\n" +
                "               C.DEPT_ID,\n" +
                "               (SELECT NAME FROM MAS_DEPT A WHERE A.ID = C.DEPT_ID) DEPT_NAME,\n" +
                "               C.COMPANY_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = C.COMPANY_ID) COMPANY_NAME,\n" +
                "                '其他' accountType, \n" +
                "               C.DATE_APPROVE\n" +
                "          FROM ADVANCE_PAYMENT_METHOD B, ADVANCE_PAYMENT C\n" +
                "         WHERE B.payment_id = C.ID\n" +
                "           AND C.STATE = 'approve'\n" +
                "           AND B.PAYMENT_METHOD_TYPE IN ('09')\n" +
                "           AND (B.CUSTOMER_ID = 'C0005998' OR EXISTS(SELECT 1\n" +
                "             FROM MAS_PARTY_ROLE R\n" +
                "             WHERE R.PARTY_ID=(SELECT MPR.PARTY_ID FROM MAS_PARTY_ROLE MPR WHERE MPR.ID=B.CUSTOMER_ID) and R.ROLE_ID='Company'\n" +
                "              ))      AND ((SELECT t.Party_Id\n" +
                "                      FROM MAS_PARTY_ROLE t\n" +
                "                      WHERE T.ID = C.COMPANY_ID) !=\n" +
                "                     (SELECT t.Party_Id\n" +
                "                      FROM MAS_PARTY_ROLE t\n" +
                "                      WHERE T.ID = B.CUSTOMER_ID)) \n" +
                "         UNION ALL\n " +
                "         SELECT B.PRICE_TOTAL AMOUNT, A.DEPT_ID,\n" +
                "          (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID) DEPT_NAME,\n" +
                "               A.COMPANY_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = A.COMPANY_ID) COMPANY_NAME,\n" +
                "                '平台内客户' accountType ,\n" +
                "                A.CREATE_DATE DATE_APPROVE\n"
                                +C_IN_PLATFORM_CLIENT +
                "         UNION ALL\n" +
                "      SELECT AMOUNT,\n" +
                "           A.DEPT_ID,\n" +
                "           (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID) DEPT_NAME,\n" +
                "            A.PAYMENT_ID AS COMPANY_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = A.PAYMENT_ID) COMPANY_NAME,\n" +
                "                '定损介绍费' accountType ,\n" +
                "                 A.CREATE_DATE DATE_APPROVE\n" +
                "      FROM FN_SETTLE_EXPENSE A\n" +
                "     WHERE A.EXPENSE_TYPE = 'infoExpense'\n" +
                "       AND A.TYPE = 'in'" +
                "UNION ALL\n" +
                "SELECT B.FN_COMMISSION AS AMOUNT ,\n" +
                " A.DEPT_ID AS DEPT_ID,\n" +
                "  (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID) DEPT_NAME,\n" +
                "       A.COMPANY_ID,\n" +
                "       (SELECT C.NAME\n" +
                "          FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "         WHERE C.ID = D.PARTY_ID\n" +
                "           AND D.ID = A.COMPANY_ID) COMPANY_NAME, '管理服务费' accountType,\n" +
                "       A.CREATE_DATE AS DATE_APPROVE\n" +
                "  FROM POLICY_INFORMATION A, POLICY_INFORMATION_ITEM B\n" +
                " WHERE A.ID = B.POLICY_INFORMATION_ID" +
                "                UNION ALL\n" +
                "                SELECT A.STORE_PRICE AS AMOUNT,A.DEPT_ID, (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID) DEPT_NAME,\n" +
                "                 B.COMPANY_ID, \n" +
                "                (SELECT C.NAME FROM MAS_PARTY C, MAS_PARTY_ROLE D WHERE C.ID = D.PARTY_ID AND D.ID = B.COMPANY_ID) COMPANY_NAME,\n" +
                "                 '二手车服务费' accountType,A.date_approve as DATE_APPROVE\n" +
                "                  FROM WAR_USED_CAR A ,MAS_DEPT B\n" +
                "\n" +
                "                    WHERE A.DEPT_ID = B.ID\n" +
                "                 AND A.STATE = 'approve' " +
                " UNION ALL " +
                " SELECT B.AMOUNT,\n" +
                "               C.DEPT_ID,\n" +
                "               (SELECT NAME FROM MAS_DEPT A WHERE A.ID = C.DEPT_ID) DEPT_NAME,\n" +
                "               C.COMPANY_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = C.COMPANY_ID) COMPANY_NAME,\n" +
                "               'e服务+' accountType,    \n" +
                "               C.DATE_APPROVE\n" +
                "          FROM RETAIL_ORDER_PAYMENT_METHOD B, RETAIL_ORDER C, RETAIL_ORDER_ITEM E\n" +
                "         WHERE B.RETAIL_ORDER_ID = C.ID\n" +
                "           AND B.PAYMENT_METHOD_TYPE IN ('09')\n" +
                "           AND C.CLAIM_TYPE IS NULL\n" +
                "           AND (B.CUSTOMER_ID = 'C0005998' OR EXISTS(SELECT 1\n" +
                "             FROM MAS_PARTY_ROLE R\n" +
                "             WHERE R.PARTY_ID=(SELECT MPR.PARTY_ID FROM MAS_PARTY_ROLE MPR WHERE MPR.ID=B.CUSTOMER_ID) and R.ROLE_ID='Company'\n" +
                "              ))      AND ((SELECT t.Party_Id\n" +
                "                      FROM MAS_PARTY_ROLE t\n" +
                "                      WHERE T.ID = C.COMPANY_ID) !=\n" +
                "                     (SELECT t.Party_Id\n" +
                "                      FROM MAS_PARTY_ROLE t\n" +
                "                      WHERE T.ID = B.CUSTOMER_ID))\n" +
                "           AND E.PRODUCT_ID IN ('0002935068','0002902247','0002935072','0003020877','0002902250') " +
                "           AND E.SALE_ID = C.ID " +
                " UNION ALL" +
                "         SELECT B.PRICE_TOTAL AMOUNT, A.DEPT_ID,\n" +
                "          (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID) DEPT_NAME,\n" +
                "               A.COMPANY_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = A.COMPANY_ID) COMPANY_NAME,\n" +
                "                'e服务' accountType ,\n" +
                "                A.CREATE_DATE DATE_APPROVE\n" +
                "      FROM ACC_VOUCHER a, ACC_VOUCHER_ITEM B\n" +
                "     WHERE A.ID = B.VOUCHER_ID\n" +
                "       AND A.VOUCHER_TYPE = 'CUSTOMER_SALE'" +
                "       AND a.ORIGIN_BUSINESS_TYPE<>'ProfitAndLoss'" +
                "       AND B.PRODUCT_ID = '0002902284'\n" +
                "       AND ((SELECT t.Party_Id\n" +
                "               FROM MAS_PARTY_ROLE t\n" +
                "              WHERE T.ID = A.COMPANY_ID) !=\n" +
                "           (SELECT t.Party_Id\n" +
                "               FROM MAS_PARTY_ROLE t\n" +
                "              WHERE T.ID = A.PARTNER_ID))\n" +
                " )\n" +
                "          \n" +
                " WHERE 1 = 1");
        JSONArray DATE_OF_STATISTICS = param.getJSONArray("DATE_OF_STATISTICS");
        try {
            JSONArray DATE_APPROVE_S = param.getJSONArray("DATE_APPROVE");
            if (DATE_APPROVE_S.size() == 2) {
                DATE_OF_STATISTICS = DATE_APPROVE_S;
                param.put("DATE_APPROVE", null);
            }
        } catch (Exception e) {

        }
        if (DATE_OF_STATISTICS!=null) {
            String DATE_OF_STATISTICS_S = DATE_OF_STATISTICS.getString(0);
            Date DATE_OF_STATISTICS_STATE = StringToDate(DATE_OF_STATISTICS_S,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_STATE_S = DateUtil.format(DATE_OF_STATISTICS_STATE,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_STATE",DATE_OF_STATISTICS_STATE_S);
            String DATE_OF_STATISTICS_E = DATE_OF_STATISTICS.getString(1);
            Date DATE_OF_STATISTICS_END = StringToDate(DATE_OF_STATISTICS_E,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_END_S = DateUtil.format(DATE_OF_STATISTICS_END,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_END",DATE_OF_STATISTICS_END_S);
        }
        DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        if(param.get("DATE_OF_STATISTICS_STATE") != null)  stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd')  ");
        if(param.get("DATE_OF_STATISTICS_END") != null)  stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");        if(param.get("COMPANY_ID") != null ) stringBuffer.append(" AND COMPANY_ID = :COMPANY_ID ");
        if(param.get("DEPT_ID") != null )  stringBuffer.append(" AND DEPT_ID = :DEPT_ID ");
        if(param.get("PAYMENT_METHOD_TYPE") != null)  stringBuffer.append(" AND PAYMENT_METHOD_TYPE  = :PAYMENT_METHOD_TYPE ");
        if(param.get("DATE_APPROVE") != null) {
            try {
                Date date = formatter.parse(param.getString("DATE_APPROVE"));
                stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_APPROVE,'yyyy-MM-dd') ");
                stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_APPROVE,'yyyy-MM-dd') +1");
            } catch (Exception e) {
                Date start = formatter.parse(param.getString("DATE_OF_STATISTICS_STATE"));
                Date end = formatter.parse(param.getString("DATE_OF_STATISTICS_END"));
                LocalDate localDateStart = DateUtil.UDateToLocalDate(start);
                LocalDate localDateEnd = DateUtil.UDateToLocalDate(end);
                if (localDateStart.getMonthValue() == localDateEnd.getMonthValue()) {
                    stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd') ");
                    stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
                } else {
                    formatter = new SimpleDateFormat("yyyy-MM");
                    Date dateApprove = formatter.parse(param.getString("DATE_APPROVE"));
                    LocalDate loDateApprove = DateUtil.UDateToLocalDate(dateApprove);
                    if (localDateStart.getMonthValue() == loDateApprove.getMonthValue()){
                        //获取月末
                        LocalDate monthOfLastDate = loDateApprove.with(TemporalAdjusters.lastDayOfMonth());
                        String SmonthOfLastDate = monthOfLastDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                        param.put("SmonthOfLastDate",SmonthOfLastDate);
                        stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd') ");
                        stringBuffer.append(" AND DATE_APPROVE < to_date(:SmonthOfLastDate,'yyyy-MM-dd') + 1");
                    } else {
                        //获取月头
                        LocalDate monthOfFirstDate = loDateApprove.with(TemporalAdjusters.firstDayOfMonth());
                        LocalDate monthOfLastDate = loDateApprove.with(TemporalAdjusters.lastDayOfMonth());
                        String SmonthOfFirstDate = monthOfFirstDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                        String monthOfLastDateStr = monthOfLastDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                        param.put("SmonthOfFirstDate",SmonthOfFirstDate);
                        param.put("SmonthOfLastDate",monthOfLastDateStr);
                        stringBuffer.append(" AND DATE_APPROVE >= to_date(:SmonthOfFirstDate,'yyyy-MM-dd') ");
                        stringBuffer.append(" AND DATE_APPROVE < to_date(:SmonthOfLastDate,'yyyy-MM-dd') + 1");
                    }
                }

            }
        }
        stringBuffer.append(" GROUP BY DEPT_ID, DEPT_NAME,COMPANY_ID,COMPANY_NAME,ACCOUNTTYPE ");
        return toJSONArray(find(stringBuffer.toString(), param));
    }

    /**
     * 查询应收账款(内)明细
     * @param param
     * @return
     */
    public JSONArray find_receivableInMx(JSONObject param) throws ParseException {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("SELECT NVL(SUM(AMOUNT), 0) AMOUNT,\n" +
                "       DEPT_ID,\n" +
                "       DEPT_NAME,\n" +
                "       COMPANY_ID,\n" +
                "       COMPANY_NAME,\n" +
                "       ACCOUNTTYPE,\n" +
                "       TO_CHAR(DATE_APPROVE, 'yyyy-MM-dd') as DATE_OF_STATISTICS\n" +
                "  FROM (SELECT B.AMOUNT,\n" +
                "               C.DEPT_ID,\n" +
                "               (SELECT NAME FROM MAS_DEPT A WHERE A.ID = C.DEPT_ID) DEPT_NAME,\n" +
                "               C.COMPANY_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = C.COMPANY_ID) COMPANY_NAME,\n" +
                "               '集群引流' accountType,    \n" +
                "               C.DATE_APPROVE\n" +
                "          FROM RETAIL_ORDER_PAYMENT_METHOD B, RETAIL_ORDER C\n" +
                "         WHERE B.RETAIL_ORDER_ID = C.ID\n" +
//                "           AND C.STATE IN ('done', 'confirmed')\n" +
                "           AND B.PAYMENT_METHOD_TYPE IN ('09')\n" +
                "           AND C.CLAIM_TYPE IS NULL\n" +
                "           AND (\n" +
                "                    (B.CUSTOMER_ID IN ('K1001','K1003','C0005998','02532856','02533043') AND C.ORDER_TYPE<>'replacement' " +
                "                       AND ((SELECT t.Party_Id\n" +
                "                          FROM MAS_PARTY_ROLE t\n" +
                "                          WHERE T.ID = C.COMPANY_ID) !=\n" +
                "                         (SELECT t.Party_Id\n" +
                "                          FROM MAS_PARTY_ROLE t\n" +
                "                          WHERE T.ID = B.CUSTOMER_ID)))\n" +
                "                )\n" +
                "           AND NOT EXISTS (SELECT 1 FROM RETAIL_ORDER_ITEM E " +
            "                       WHERE E.PRODUCT_ID IN ('0002935068','0002902247','0002935072','0003020877','0002902250') " +
                "                   AND E.SALE_ID = C.ID) " +

                "        UNION ALL\n" +
                "      SELECT B.AMOUNT,\n" +
                "             C.DEPT_ID,\n" +
                "             (SELECT NAME FROM MAS_DEPT A WHERE A.ID = C.DEPT_ID) DEPT_NAME,\n" +
                "             C.COMPANY_ID,\n" +
                "             (SELECT C.NAME\n" +
                "              FROM MAS_PARTY C,\n" +
                "                   MAS_PARTY_ROLE D\n" +
                "              WHERE C.ID = D.PARTY_ID\n" +
                "                AND D.ID = C.COMPANY_ID)                          COMPANY_NAME,\n" +
                "             '兑换券置换'                                              accountType,\n" +
                "             C.DATE_APPROVE\n" +
                "      FROM RETAIL_ORDER_PAYMENT_METHOD B,\n" +
                "           RETAIL_ORDER C\n" +
                "      WHERE B.RETAIL_ORDER_ID = C.ID\n" +
                "        AND B.PAYMENT_METHOD_TYPE IN ('09')\n" +
                "        AND C.CLAIM_TYPE\n" +
                "          IS NULL\n" +
                "\n" +
                "        and (EXISTS(SELECT 1\n" +
                "                    FROM MAS_PARTY_ROLE R\n" +
                "                    WHERE R.PARTY_ID = (SELECT MPR.PARTY_ID FROM MAS_PARTY_ROLE MPR WHERE MPR.ID = B.CUSTOMER_ID)\n" +
                "                      and R.ROLE_ID = 'Company')\n" +
                "          AND C.ORDER_TYPE = 'replacement' AND ((SELECT t.Party_Id\n" +
                "                                                 FROM MAS_PARTY_ROLE t\n" +
                "                                                 WHERE T.ID = C.COMPANY_ID) !=\n" +
                "                                                (SELECT t.Party_Id FROM MAS_PARTY_ROLE t WHERE T.ID = B.CUSTOMER_ID))\n" +
                "          )\n" +
                "\n" +
                "        UNION ALL\n" +
                "        SELECT B.AMOUNT,\n" +
                "               C.DEPT_ID,\n" +
                "               (SELECT NAME FROM MAS_DEPT A WHERE A.ID = C.DEPT_ID) DEPT_NAME,\n" +
                "               C.COMPANY_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = C.COMPANY_ID) COMPANY_NAME,\n" +
                "                '其他' accountType, \n" +
                "               C.DATE_APPROVE\n" +
                "          FROM ADVANCE_PAYMENT_METHOD B, ADVANCE_PAYMENT C\n" +
                "         WHERE B.payment_id = C.ID\n" +
                "           AND C.STATE = 'approve'\n" +
                "           AND B.PAYMENT_METHOD_TYPE IN ('09')\n" +
                "           AND (B.CUSTOMER_ID = 'C0005998' OR EXISTS(SELECT 1\n" +
                "             FROM MAS_PARTY_ROLE R\n" +
                "             WHERE R.PARTY_ID=(SELECT MPR.PARTY_ID FROM MAS_PARTY_ROLE MPR WHERE MPR.ID=B.CUSTOMER_ID) and R.ROLE_ID='Company'\n" +
                "              ))      AND ((SELECT t.Party_Id\n" +
                "                      FROM MAS_PARTY_ROLE t\n" +
                "                      WHERE T.ID = C.COMPANY_ID) !=\n" +
                "                     (SELECT t.Party_Id\n" +
                "                      FROM MAS_PARTY_ROLE t\n" +
                "                      WHERE T.ID = B.CUSTOMER_ID))\n" +
                "         UNION ALL\n"      +
                "         SELECT B.PRICE_TOTAL AMOUNT, A.DEPT_ID,\n" +
                "          (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID) DEPT_NAME,\n" +
                "               A.COMPANY_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = A.COMPANY_ID) COMPANY_NAME,\n" +
                "                '平台内客户' accountType ,\n" +
                "                A.CREATE_DATE DATE_APPROVE\n"
                + C_IN_PLATFORM_CLIENT +
                "         UNION ALL\n" +
                "      SELECT AMOUNT,\n" +
                "           A.DEPT_ID,\n" +
                "           (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID) DEPT_NAME,\n" +
                "            A.PAYMENT_ID AS COMPANY_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = A.PAYMENT_ID) COMPANY_NAME,\n" +
                "                '定损介绍费' accountType ,\n" +
                "                 A.CREATE_DATE DATE_APPROVE\n" +
                "      FROM FN_SETTLE_EXPENSE A\n" +
                "     WHERE A.EXPENSE_TYPE = 'infoExpense'\n" +
                "       AND A.TYPE = 'in'" +
                "UNION ALL\n" +
                "SELECT B.FN_COMMISSION AS AMOUNT ,\n" +
                " A.DEPT_ID AS DEPT_ID,\n" +
                "  (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID) DEPT_NAME,\n" +
                "       A.COMPANY_ID,\n" +
                "       (SELECT C.NAME\n" +
                "          FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "         WHERE C.ID = D.PARTY_ID\n" +
                "           AND D.ID = A.COMPANY_ID) COMPANY_NAME, '管理服务费' accountType,\n" +
                "       A.CREATE_DATE AS DATE_APPROVE\n" +
                "  FROM POLICY_INFORMATION A, POLICY_INFORMATION_ITEM B\n" +
                " WHERE A.ID = B.POLICY_INFORMATION_ID" +
                "                UNION ALL\n" +
                "                SELECT A.STORE_PRICE AS AMOUNT,A.DEPT_ID, (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID) DEPT_NAME,\n" +
                "                 B.COMPANY_ID, \n" +
                "                (SELECT C.NAME FROM MAS_PARTY C, MAS_PARTY_ROLE D WHERE C.ID = D.PARTY_ID AND D.ID = B.COMPANY_ID) COMPANY_NAME,\n" +
                "                 '二手车服务费' accountType,A.date_approve as DATE_APPROVE\n" +
                "                  FROM WAR_USED_CAR A ,MAS_DEPT B\n" +
                "\n" +
                "                    WHERE A.DEPT_ID = B.ID\n" +
                "                 AND A.STATE = 'approve'" +
                " UNION ALL" +
                "   SELECT B.AMOUNT,\n" +
                "               C.DEPT_ID,\n" +
                "               (SELECT NAME FROM MAS_DEPT A WHERE A.ID = C.DEPT_ID) DEPT_NAME,\n" +
                "               C.COMPANY_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = C.COMPANY_ID) COMPANY_NAME,\n" +
                "               'e服务+' accountType,    \n" +
                "               C.DATE_APPROVE\n" +
                "          FROM RETAIL_ORDER_PAYMENT_METHOD B, RETAIL_ORDER C, RETAIL_ORDER_ITEM E\n" +
                "         WHERE B.RETAIL_ORDER_ID = C.ID\n" +
                "           AND B.PAYMENT_METHOD_TYPE IN ('09')\n" +
                "           AND C.CLAIM_TYPE IS NULL\n" +
                "           AND (B.CUSTOMER_ID = 'C0005998' OR EXISTS(SELECT 1\n" +
                "             FROM MAS_PARTY_ROLE R\n" +
                "             WHERE R.PARTY_ID=(SELECT MPR.PARTY_ID FROM MAS_PARTY_ROLE MPR WHERE MPR.ID=B.CUSTOMER_ID) and R.ROLE_ID='Company'\n" +
                "              ))      AND ((SELECT t.Party_Id\n" +
                "                      FROM MAS_PARTY_ROLE t\n" +
                "                      WHERE T.ID = C.COMPANY_ID) !=\n" +
                "                     (SELECT t.Party_Id\n" +
                "                      FROM MAS_PARTY_ROLE t\n" +
                "                      WHERE T.ID = B.CUSTOMER_ID))\n" +
                "           AND E.PRODUCT_ID IN ('0002935068','0002902247','0002935072','0003020877','0002902250') " +
                "           AND E.SALE_ID = C.ID " +
                " UNION ALL" +
                "         SELECT B.PRICE_TOTAL AMOUNT, A.DEPT_ID,\n" +
                "          (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID) DEPT_NAME,\n" +
                "               A.COMPANY_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = A.COMPANY_ID) COMPANY_NAME,\n" +
                "                'e服务' accountType ,\n" +
                "                A.CREATE_DATE DATE_APPROVE\n" +
                "      FROM ACC_VOUCHER a, ACC_VOUCHER_ITEM B\n" +
                "     WHERE A.ID = B.VOUCHER_ID\n" +
                "       AND A.VOUCHER_TYPE = 'CUSTOMER_SALE'" +
                "       AND a.ORIGIN_BUSINESS_TYPE<>'ProfitAndLoss'" +
                "       AND B.PRODUCT_ID = '0002902284'\n" +
                "       AND ((SELECT t.Party_Id\n" +
                "               FROM MAS_PARTY_ROLE t\n" +
                "              WHERE T.ID = A.COMPANY_ID) !=\n" +
                "           (SELECT t.Party_Id\n" +
                "               FROM MAS_PARTY_ROLE t\n" +
                "              WHERE T.ID = A.PARTNER_ID))\n" +

                " )\n" +
                "          \n" +
                " WHERE 1 = 1");
        JSONArray DATE_OF_STATISTICS = param.getJSONArray("DATE_OF_STATISTICS");
        try {
            JSONArray DATE_APPROVE_S = param.getJSONArray("DATE_APPROVE");
            if (DATE_APPROVE_S.size() == 2) {
                DATE_OF_STATISTICS = DATE_APPROVE_S;
                param.put("DATE_APPROVE", null);
            }
        } catch (Exception e) {

        }
        if (DATE_OF_STATISTICS!=null) {
            String DATE_OF_STATISTICS_S = DATE_OF_STATISTICS.getString(0);
            Date DATE_OF_STATISTICS_STATE = StringToDate(DATE_OF_STATISTICS_S,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_STATE_S = DateUtil.format(DATE_OF_STATISTICS_STATE,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_STATE",DATE_OF_STATISTICS_STATE_S);
            String DATE_OF_STATISTICS_E = DATE_OF_STATISTICS.getString(1);
            Date DATE_OF_STATISTICS_END = StringToDate(DATE_OF_STATISTICS_E,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_END_S = DateUtil.format(DATE_OF_STATISTICS_END,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_END",DATE_OF_STATISTICS_END_S);
        }
        DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        if(param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_STATE") != null)  stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd')  ");
        if(param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_END") != null)  stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1 ");
        if(param.get("COMPANY_ID") != null ) stringBuffer.append(" AND COMPANY_ID = :COMPANY_ID ");
        if(param.get("DEPT_ID") != null )  stringBuffer.append(" AND DEPT_ID = :DEPT_ID ");
        if(param.get("ACCOUNTTYPE") != null)  stringBuffer.append(" AND ACCOUNTTYPE  = :ACCOUNTTYPE ");
        if(param.get("DATE_APPROVE") != null) {
            try {
                Date date = formatter.parse(param.getString("DATE_APPROVE"));
                stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_APPROVE,'yyyy-MM-dd') ");
                stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_APPROVE,'yyyy-MM-dd') +1");
            } catch (Exception e) {
                if (!param.containsKey("DATE_OF_STATISTICS_STATE")){
                    formatter = new SimpleDateFormat("yyyy-MM");
                    Date dateApprove = formatter.parse(param.getString("DATE_APPROVE"));
                    LocalDate loDateApprove = DateUtil.UDateToLocalDate(dateApprove);
                    LocalDate monthOfFirstDate = loDateApprove.with(TemporalAdjusters.firstDayOfMonth());
                    LocalDate monthOfLastDate = loDateApprove.with(TemporalAdjusters.lastDayOfMonth());
                    String SmonthOfFirstDate = monthOfFirstDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                    String monthOfLastDateStr = monthOfLastDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                    param.put("SmonthOfFirstDate",SmonthOfFirstDate);
                    param.put("DATE_OF_STATISTICS_END",monthOfLastDateStr);
                    stringBuffer.append(" AND DATE_APPROVE >= to_date(:SmonthOfFirstDate,'yyyy-MM-dd') ");
                    stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");

                }else {
                    Date start = formatter.parse(param.getString("DATE_OF_STATISTICS_STATE"));
                    Date end = formatter.parse(param.getString("DATE_OF_STATISTICS_END"));
                    LocalDate localDateStart = DateUtil.UDateToLocalDate(start);
                    LocalDate localDateEnd = DateUtil.UDateToLocalDate(end);
                    if (localDateStart.getMonthValue() == localDateEnd.getMonthValue()) {
                        stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd') ");
                        stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
                    } else {
                        formatter = new SimpleDateFormat("yyyy-MM");
                        Date dateApprove = formatter.parse(param.getString("DATE_APPROVE"));
                        LocalDate loDateApprove = DateUtil.UDateToLocalDate(dateApprove);
                        if (localDateStart.getMonthValue() == loDateApprove.getMonthValue()){
                            //获取月末
                            LocalDate monthOfLastDate = loDateApprove.with(TemporalAdjusters.lastDayOfMonth());
                            String SmonthOfLastDate = monthOfLastDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                            param.put("SmonthOfLastDate",SmonthOfLastDate);
                            stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd') ");
                            stringBuffer.append(" AND DATE_APPROVE < to_date(:SmonthOfLastDate,'yyyy-MM-dd') + 1");
                        } else {
                            //获取月头
                            LocalDate monthOfFirstDate = loDateApprove.with(TemporalAdjusters.firstDayOfMonth());
                            String SmonthOfFirstDate = monthOfFirstDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                            param.put("SmonthOfFirstDate",SmonthOfFirstDate);
                            stringBuffer.append(" AND DATE_APPROVE >= to_date(:SmonthOfFirstDate,'yyyy-MM-dd') ");
                            stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
                        }
                    }
                }
            }
        }
        stringBuffer.append(" GROUP BY DEPT_ID, DEPT_NAME,COMPANY_ID,COMPANY_NAME,ACCOUNTTYPE,TO_CHAR(DATE_APPROVE, 'yyyy-MM-dd') ");
        stringBuffer.append(" order BY TO_CHAR(DATE_APPROVE, 'yyyy-MM-dd')");
        return toJSONArray(find(stringBuffer.toString(), param));
    }

    /**
     * 查询收款明细
     * @param param
     * @return
     */
    public JSONArray find_receivableInSKMxGdj(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append(" \n" +
                "SELECT TO_CHAR(DATE_APPROVE, 'yyyy-MM-dd') as DATE_OF_STATISTICS,\n" +
                "       DEPT_ID,\n" +
                "       DEPT_NAME,\n" +
                "       SOURCE_ID AS ID,\n" +
                "       CAR_LICENSE,\n" +
                "           (GET_ENCRYPT_MOBILE(MOBILE)) AS MOBILE ,\n" +
                "       PAYMENT_METHOD_TYPE_NAME,\n" +
                "       PAYMENT_METHOD_TYPE,\n" +
                "       NVL(SUM(AMOUNT), 0) AMOUNT,\n" +
                "       CUSTOMER_ID,\n" +
                "       CUSTOMER_NAME,\n" +
                "       CROSSIC_ID,\n" +
                "        (SELECT C.NAME\n" +
                "             FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "            WHERE C.ID = D.PARTY_ID\n" +
                "              AND D.ID = crossIC_id)\n" +
                "        CROSSIC_NAME,\n" +
                "       COMPANY_ID,\n" +
                "       COMPANY_NAME,\n" +
                "       FORM_ID,API,"+
                "       ACCOUNTTYPE\n" +
                "  FROM (SELECT B.AMOUNT,\n" +
                "               C.DEPT_ID,\n" +
                "               (SELECT NAME FROM MAS_DEPT A WHERE A.ID = C.DEPT_ID) DEPT_NAME,\n" +
                "               C.COMPANY_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = C.COMPANY_ID) COMPANY_NAME,\n" +
                "               B.CUSTOMER_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = B.CUSTOMER_ID) CUSTOMER_NAME,\n" +
                "               C.DATE_APPROVE,\n" +
                "               C.ID SOURCE_ID,\n" +
                "               (SELECT T.CAR_LICENSE\n" +
                "                  FROM FN_CUSTOMER_EXPAND T\n" +
                "                 WHERE T.ID = C.CUSTOMER_EXPAND_ID) CAR_LICENSE,\n" +
                "               (SELECT (GET_ENCRYPT_MOBILE(T.MOBILE)) AS MOBILE\n" +
                "                  FROM FN_CUSTOMER_EXPAND T\n" +
                "                 WHERE T.ID = C.CUSTOMER_EXPAND_ID) MOBILE,\n" +
                "              '' crossIC_id,\n" +
                "               B.PAYMENT_METHOD_TYPE,\n" +
                "               (SELECT t.NAME\n" +
                "                  FROM mas_payment_method t\n" +
                "                 WHERE T.ID = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_TYPE_NAME,\n" +
                "               'form/retail.order' FORM_ID,'retail.order.findOne' API,"+
                "               '零售' accountType\n" +
                "          FROM RETAIL_ORDER_PAYMENT_METHOD B,\n" +
                "               RETAIL_ORDER                C\n" +
                 "       WHERE B.RETAIL_ORDER_ID = C.ID  \n" +
//                "           AND C.STATE IN ('done', 'confirmed')\n" +
                "           AND C.CLAIM_TYPE IS NULL "+
                "        UNION ALL\n" +
                "        SELECT B.AMOUNT,\n" +
                "               C.DEPT_ID,\n" +
                "               (SELECT NAME FROM MAS_DEPT A WHERE A.ID = C.DEPT_ID) DEPT_NAME,\n" +
                "               C.COMPANY_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = C.COMPANY_ID) COMPANY_NAME,\n" +
                "               B.CUSTOMER_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = B.CUSTOMER_ID) CUSTOMER_NAME,\n" +
                "               C.DATE_APPROVE,\n" +
                "               C.ID SOURCE_ID,\n" +
                "               (SELECT T.CAR_LICENSE\n" +
                "                  FROM FN_CUSTOMER_EXPAND T\n" +
                "                 WHERE T.ID = C.CUSTOMER_EXPAND_ID) CAR_LICENSE,\n" +
                "               (SELECT (GET_ENCRYPT_MOBILE(T.MOBILE)) AS MOBILE\n" +
                "                  FROM FN_CUSTOMER_EXPAND T\n" +
                "                 WHERE T.ID = C.CUSTOMER_EXPAND_ID) MOBILE,\n" +
                "               NULL crossIC_id,\n" +
                "               B.PAYMENT_METHOD_TYPE,\n" +
                "               (SELECT t.NAME\n" +
                "                  FROM mas_payment_method t\n" +
                "                 WHERE T.ID = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_TYPE_NAME,\n" +
                "               'form/advance.payment' FORM_ID,'advance.payment.findOne' API,"+
                "               '订金' accountType\n" +
                "          FROM ADVANCE_PAYMENT_METHOD B, ADVANCE_PAYMENT C\n" +
                "         WHERE B.payment_id = C.ID\n" +
                "           AND C.STATE = 'approve'\n" +
                "        UNION ALL\n" +
                "      SELECT B.INCOME_AMOUNT,  D.ID DEPT_ID,D.NAME DEPT_NAME,C.COMPANY_ID,\n" +
                "      (SELECT C.NAME  FROM MAS_PARTY C, MAS_PARTY_ROLE D WHERE C.ID = D.PARTY_ID AND D.ID = C.COMPANY_ID) COMPANY_NAME,\n" +
                "     '' CUSTOMER_ID,'' CUSTOMER_NAME,\n" +
                "     C.APPROVE_DATE,C.ID SOURCE_ID,\n" +
                "    (SELECT T.CAR_LICENSE  FROM FN_CUSTOMER_EXPAND T WHERE T.ID = C.CUSTOMER_EXPAND_ID) CAR_LICENSE,\n" +
                "    (SELECT (GET_ENCRYPT_MOBILE(T.MOBILE)) AS MOBILE FROM FN_CUSTOMER_EXPAND T WHERE T.ID = C.CUSTOMER_EXPAND_ID) MOBILE,\n" +
                "    NULL crossIC_id, B.PAYMENT_METHOD_TYPE,\n" +
                "       (SELECT t.NAME FROM mas_payment_method T WHERE T.ID = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_TYPE_NAME," +
                "     'form/money.income.receipts' FORM_ID,'money.income.receipts.findOne' API,"+
                "     '资金收入' accountType\n" +
                "    FROM money_income C, money_income_item B,mas_dept D\n" +
                "    WHERE C.id = B.MONEY_INCOME and C.DEPT_ID = D.id\n" +
                "    AND C.STATE = 'approve' )\n" +
                "    WHERE 1 = 1");


        JSONArray DATE_OF_STATISTICS = param.getJSONArray("DATE_OF_STATISTICS");
        if (DATE_OF_STATISTICS!=null) {
            String DATE_OF_STATISTICS_S = DATE_OF_STATISTICS.getString(0);
            Date DATE_OF_STATISTICS_STATE = StringToDate(DATE_OF_STATISTICS_S,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_STATE_S = DateUtil.format(DATE_OF_STATISTICS_STATE,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_STATE",DATE_OF_STATISTICS_STATE_S);
            String DATE_OF_STATISTICS_E = DATE_OF_STATISTICS.getString(1);
            Date DATE_OF_STATISTICS_END = StringToDate(DATE_OF_STATISTICS_E,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_END_S = DateUtil.format(DATE_OF_STATISTICS_END,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_END",DATE_OF_STATISTICS_END_S);
        }
        if(param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_STATE") != null)  stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd')  ");
        if(param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_END") != null)  stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1 ");
        if(param.get("COMPANY_ID") != null ) stringBuffer.append(" AND COMPANY_ID = :COMPANY_ID ");
        if(param.get("DEPT_ID") != null )  stringBuffer.append(" AND DEPT_ID = :DEPT_ID ");
        if(param.get("ACCOUNTTYPE") != null )  stringBuffer.append(" AND ACCOUNTTYPE = :ACCOUNTTYPE ");
        if(param.get("ACCOUNTTYPE") != null )  stringBuffer.append(" AND ACCOUNTTYPE = :ACCOUNTTYPE ");
        if(param.get("PAYMENT_METHOD_TYPE") != null)  stringBuffer.append(" AND PAYMENT_METHOD_TYPE  = :PAYMENT_METHOD_TYPE ");
        if(param.get("DATE_APPROVE") != null)  stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_APPROVE,'yyyy-MM-dd')");
        if(param.get("DATE_APPROVE") != null)  stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_APPROVE,'yyyy-MM-dd') +1");
        stringBuffer.append(" GROUP BY DEPT_ID,\n" +
                "          DEPT_NAME,\n" +
                "          COMPANY_ID,\n" +
                "          COMPANY_NAME,\n" +
                "          CUSTOMER_ID,\n" +
                "          CUSTOMER_NAME,\n" +
                "          TO_CHAR(DATE_APPROVE, 'yyyy-MM-dd'),\n" +
                "          SOURCE_ID,\n" +
                "          CAR_LICENSE,\n" +
                "          MOBILE,\n" +
                "          CROSSIC_ID,\n" +
                "          PAYMENT_METHOD_TYPE_NAME,\n" +
                "          FORM_ID,API," +
                "          ACCOUNTTYPE,\n" +
                "          PAYMENT_METHOD_TYPE ");

        return toJSONArray(find(stringBuffer.toString(), param));
    }


    /**
     * 查询应收账款(内)明细 集群引流与其他
     * @param param
     * @return
     */
    public JSONArray find_receivableInMxGdj(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append(" \n" +
                "SELECT TO_CHAR(DATE_APPROVE, 'yyyy-MM-dd') as DATE_OF_STATISTICS,\n" +
                "       DEPT_ID,\n" +
                "       DEPT_NAME,\n" +
                "       SOURCE_ID AS ID,\n" +
                "       CAR_LICENSE,\n" +
                "       (GET_ENCRYPT_MOBILE(MOBILE)) AS MOBILE,\n" +
                "       PAYMENT_METHOD_TYPE_NAME,\n" +
                "       PAYMENT_METHOD_TYPE,\n" +
                "       NVL(SUM(AMOUNT), 0) AMOUNT,\n" +
                "       CUSTOMER_ID,\n" +
                "       CUSTOMER_NAME,\n" +
                "       CROSSIC_ID,\n" +
                "        (SELECT C.NAME\n" +
                "             FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "            WHERE C.ID = D.PARTY_ID\n" +
                "              AND D.ID = crossIC_id)\n" +
                "        CROSSIC_NAME,\n" +
                "       COMPANY_ID,\n" +
                "       COMPANY_NAME,\n" +
                "       FORM_ID,API,"+
                "       ACCOUNTTYPE\n" +
                "  FROM (SELECT B.AMOUNT,\n" +
                "               C.DEPT_ID,\n" +
                "               (SELECT NAME FROM MAS_DEPT A WHERE A.ID = C.DEPT_ID) DEPT_NAME,\n" +
                "               C.COMPANY_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = C.COMPANY_ID) COMPANY_NAME,\n" +
                "               B.CUSTOMER_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = B.CUSTOMER_ID) CUSTOMER_NAME,\n" +
                "               C.DATE_APPROVE,\n" +
                "               C.ID SOURCE_ID,\n" +
                "               (SELECT T.CAR_LICENSE\n" +
                "                  FROM FN_CUSTOMER_EXPAND T\n" +
                "                 WHERE T.ID = C.CUSTOMER_EXPAND_ID) CAR_LICENSE,\n" +
                "               (SELECT (GET_ENCRYPT_MOBILE(T.MOBILE)) \n" +
                "                  FROM FN_CUSTOMER_EXPAND T\n" +
                "                 WHERE T.ID = C.CUSTOMER_EXPAND_ID) MOBILE,\n" +
                "              (SELECT distinct M.partner_id  FROM retail_order_discount t,retail_order_item f,mas_cross_ic M\n" +
                "                                WHERE T.retail_item_id = f.ID and c.id= f.sale_id and t.crossIC_id = m.id ) crossIC_id,\n" +
                "               B.PAYMENT_METHOD_TYPE,\n" +
                "               (SELECT t.NAME\n" +
                "                  FROM mas_payment_method t\n" +
                "                 WHERE T.ID = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_TYPE_NAME,\n" +
                "               'form/retail.order' FORM_ID,'retail.order.findOne' API,"+
                "               '集群引流' accountType\n" +
                "          FROM RETAIL_ORDER_PAYMENT_METHOD B,\n" +
                "               RETAIL_ORDER                C\n" +
                "         WHERE B.RETAIL_ORDER_ID = C.ID\n" +
//                "           AND C.STATE IN ('done', 'confirmed')\n" +
                "           AND B.PAYMENT_METHOD_TYPE IN ('09')\n" +
                "           AND C.CLAIM_TYPE IS NULL\n" +
                "           AND(\n" +
                "                    (B.CUSTOMER_ID IN ('K1001','K1003','C0005998','02532856','02533043') AND C.ORDER_TYPE<>'replacement'" +
                "                        AND ((SELECT t.Party_Id\n" +
                "                          FROM MAS_PARTY_ROLE t\n" +
                "                          WHERE T.ID = C.COMPANY_ID) !=\n" +
                "                         (SELECT t.Party_Id\n" +
                "                          FROM MAS_PARTY_ROLE t\n" +
                "                          WHERE T.ID = B.CUSTOMER_ID)) )\n" +
                "                )" +
                "                   AND NOT EXISTS (SELECT 1 FROM RETAIL_ORDER_ITEM E " +
                "                       WHERE E.PRODUCT_ID IN ('0002935068','0002902247','0002935072','0003020877','0002902250') " +
                "                   AND E.SALE_ID = C.ID)"+
                "        UNION ALL\n" +
                "          SELECT B.AMOUNT,\n" +
                "             C.DEPT_ID,\n" +
                "             (SELECT NAME\n" +
                "              FROM MAS_DEPT A\n" +
                "              WHERE A.ID = C.DEPT_ID)             DEPT_NAME,\n" +
                "             C.COMPANY_ID,\n" +
                "             (SELECT C.NAME\n" +
                "              FROM MAS_PARTY C,\n" +
                "                   MAS_PARTY_ROLE D\n" +
                "              WHERE C.ID = D.PARTY_ID\n" +
                "                AND D.ID = C.COMPANY_ID)          COMPANY_NAME,\n" +
                "             B.CUSTOMER_ID,\n" +
                "             (SELECT C.NAME\n" +
                "              FROM MAS_PARTY C,\n" +
                "                   MAS_PARTY_ROLE D\n" +
                "              WHERE C.ID = D.PARTY_ID\n" +
                "                AND D.ID = B.CUSTOMER_ID)\n" +
                "                                                  CUSTOMER_NAME,\n" +
                "             C.DATE_APPROVE,\n" +
                "             C.ID                                 SOURCE_ID,\n" +
                "             (SELECT T.CAR_LICENSE\n" +
                "              FROM FN_CUSTOMER_EXPAND T\n" +
                "              WHERE T.ID = C.CUSTOMER_EXPAND_ID)  CAR_LICENSE,\n" +
                "             (SELECT (GET_ENCRYPT_MOBILE(T.MOBILE))\n" +
                "              FROM FN_CUSTOMER_EXPAND T\n" +
                "              WHERE T.ID = C.CUSTOMER_EXPAND_ID)  MOBILE,\n" +
                "             (SELECT distinct M.partner_id\n" +
                "              FROM retail_order_discount t,\n" +
                "                   retail_order_item f,\n" +
                "                   mas_cross_ic M\n" +
                "              WHERE T.retail_item_id = f.ID\n" +
                "                and c.id = f.sale_id\n" +
                "                and t.crossIC_id = m.id)          crossIC_id,\n" +
                "             B.PAYMENT_METHOD_TYPE,\n" +
                "             (SELECT t.NAME\n" +
                "              FROM mas_payment_method t\n" +
                "              WHERE T.ID = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_TYPE_NAME,\n" +
                "             'form/retail.order'\n" +
                "                                                  FORM_ID,\n" +
                "             'retail.order.findOne'               API,\n" +
                "             '兑换券置换'                               accountType\n" +
                "      FROM RETAIL_ORDER_PAYMENT_METHOD B,\n" +
                "           RETAIL_ORDER C\n" +
                "      WHERE B.RETAIL_ORDER_ID = C.ID\n" +
                "        AND B.PAYMENT_METHOD_TYPE IN ('09')\n" +
                "        AND C.CLAIM_TYPE\n" +
                "          IS NULL\n" +
                "        and (EXISTS(SELECT 1\n" +
                "                    FROM MAS_PARTY_ROLE R\n" +
                "                    WHERE R.PARTY_ID\n" +
                "                        = (SELECT MPR.PARTY_ID FROM MAS_PARTY_ROLE MPR WHERE MPR.ID = B.CUSTOMER_ID)\n" +
                "                      and R.ROLE_ID\n" +
                "                        = 'Company') AND C.ORDER_TYPE = 'replacement' AND ((SELECT t.Party_Id\n" +
                "                                                                            FROM MAS_PARTY_ROLE t\n" +
                "                                                                            WHERE T.ID = C.COMPANY_ID) !=\n" +
                "                                                                           (SELECT t.Party_Id FROM MAS_PARTY_ROLE t WHERE T.ID = B.CUSTOMER_ID))\n" +
                "          )" +
                "   UNION ALL\n" +
                "        SELECT B.AMOUNT,\n" +
                "               C.DEPT_ID,\n" +
                "               (SELECT NAME FROM MAS_DEPT A WHERE A.ID = C.DEPT_ID) DEPT_NAME,\n" +
                "               C.COMPANY_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = C.COMPANY_ID) COMPANY_NAME,\n" +
                "               B.CUSTOMER_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = B.CUSTOMER_ID) CUSTOMER_NAME,\n" +
                "               C.DATE_APPROVE,\n" +
                "               C.ID SOURCE_ID,\n" +
                "               (SELECT T.CAR_LICENSE\n" +
                "                  FROM FN_CUSTOMER_EXPAND T\n" +
                "                 WHERE T.ID = C.CUSTOMER_EXPAND_ID) CAR_LICENSE,\n" +
                "               (SELECT (GET_ENCRYPT_MOBILE(T.MOBILE)) AS MOBILE \n" +
                "                  FROM FN_CUSTOMER_EXPAND T\n" +
                "                 WHERE T.ID = C.CUSTOMER_EXPAND_ID) MOBILE,\n" +
                "               NULL crossIC_id,\n" +
                "               B.PAYMENT_METHOD_TYPE,\n" +
                "               (SELECT t.NAME\n" +
                "                  FROM mas_payment_method t\n" +
                "                 WHERE T.ID = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_TYPE_NAME,\n" +
                "               'form/advance.payment' FORM_ID,'advance.payment.findOne' API,"+
                "               '其他' accountType\n" +
                "          FROM ADVANCE_PAYMENT_METHOD B, ADVANCE_PAYMENT C\n" +
                "         WHERE B.payment_id = C.ID\n" +
                "           AND C.STATE = 'approve'\n" +
                "           AND B.PAYMENT_METHOD_TYPE IN ('09')\n" +
                "           AND (B.CUSTOMER_ID = 'C0005998' OR EXISTS(SELECT 1\n" +
                "             FROM MAS_PARTY_ROLE R\n" +
                "             WHERE R.PARTY_ID=(SELECT MPR.PARTY_ID FROM MAS_PARTY_ROLE MPR WHERE MPR.ID=B.CUSTOMER_ID) and R.ROLE_ID='Company'\n" +
                "              ))      AND ((SELECT t.Party_Id\n" +
                "                      FROM MAS_PARTY_ROLE t\n" +
                "                      WHERE T.ID = C.COMPANY_ID) !=\n" +
                "                     (SELECT t.Party_Id\n" +
                "                      FROM MAS_PARTY_ROLE t\n" +
                "                      WHERE T.ID = B.CUSTOMER_ID))\n" +
                "        )\n" +
                "   WHERE 1=1 ");


        JSONArray DATE_OF_STATISTICS = param.getJSONArray("DATE_OF_STATISTICS");
        if (DATE_OF_STATISTICS!=null) {
            String DATE_OF_STATISTICS_S = DATE_OF_STATISTICS.getString(0);
            Date DATE_OF_STATISTICS_STATE = StringToDate(DATE_OF_STATISTICS_S,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_STATE_S = DateUtil.format(DATE_OF_STATISTICS_STATE,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_STATE",DATE_OF_STATISTICS_STATE_S);
            String DATE_OF_STATISTICS_E = DATE_OF_STATISTICS.getString(1);
            Date DATE_OF_STATISTICS_END = StringToDate(DATE_OF_STATISTICS_E,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_END_S = DateUtil.format(DATE_OF_STATISTICS_END,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_END",DATE_OF_STATISTICS_END_S);
        }
        if(param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_STATE") != null)  stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd')  ");
        if(param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_END") != null)  stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1 ");
        if(param.get("COMPANY_ID") != null ) stringBuffer.append(" AND COMPANY_ID = :COMPANY_ID ");
        if(param.get("DEPT_ID") != null )  stringBuffer.append(" AND DEPT_ID = :DEPT_ID ");
        if(param.get("ACCOUNTTYPE") != null )  stringBuffer.append(" AND ACCOUNTTYPE = :ACCOUNTTYPE ");
        if(param.get("DATE_APPROVE") != null)  stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_APPROVE,'yyyy-MM-dd') ");
        if(param.get("DATE_APPROVE") != null)  stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_APPROVE,'yyyy-MM-dd') +1");
        stringBuffer.append(" GROUP BY DEPT_ID,\n" +
                "          DEPT_NAME,\n" +
                "          COMPANY_ID,\n" +
                "          COMPANY_NAME,\n" +
                "          CUSTOMER_ID,\n" +
                "          CUSTOMER_NAME,\n" +
                "          TO_CHAR(DATE_APPROVE, 'yyyy-MM-dd'),\n" +
                "          SOURCE_ID,\n" +
                "          CAR_LICENSE,\n" +
                "          MOBILE,\n" +
                "          CROSSIC_ID,\n" +
                "          PAYMENT_METHOD_TYPE_NAME,\n" +
                "          FORM_ID,API,"+
                "          ACCOUNTTYPE,\n" +
                "          PAYMENT_METHOD_TYPE ");

        return toJSONArray(find(stringBuffer.toString(), param));
    }

    /**
     * 查询应收账款(内)明细 而服务+
     * @param param
     * @return
     */

    public JSONArray find_eServicePlus(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append(" \n" +
                "SELECT TO_CHAR(DATE_APPROVE, 'yyyy-MM-dd') as DATE_OF_STATISTICS,\n" +
                "       DEPT_ID,\n" +
                "       DEPT_NAME,\n" +
                "       SOURCE_ID AS ID,\n" +
                "       CAR_LICENSE,\n" +
                "       PAYMENT_METHOD_TYPE_NAME,\n" +
                "       PAYMENT_METHOD_TYPE,\n" +
                "       NVL(SUM(AMOUNT), 0) AMOUNT,\n" +
                "       CUSTOMER_ID,\n" +
                "       CUSTOMER_NAME,\n" +
                "       CROSSIC_ID,\n" +
                "        (SELECT C.NAME\n" +
                "             FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "            WHERE C.ID = D.PARTY_ID\n" +
                "              AND D.ID = crossIC_id)\n" +
                "        CROSSIC_NAME,\n" +
                "       COMPANY_ID,\n" +
                "       COMPANY_NAME,\n" +
                "       FORM_ID,API,SOURCE_BUSINESS_TYPE,"+
                "       ACCOUNTTYPE\n" +
                "  FROM (SELECT B.AMOUNT,\n" +
                "               C.DEPT_ID,\n" +
                "               (SELECT NAME FROM MAS_DEPT A WHERE A.ID = C.DEPT_ID) DEPT_NAME,\n" +
                "               C.COMPANY_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = C.COMPANY_ID) COMPANY_NAME,\n" +
                "               B.CUSTOMER_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = B.CUSTOMER_ID) CUSTOMER_NAME,\n" +
                "               C.DATE_APPROVE,\n" +
                "               C.ID SOURCE_ID,\n" +
                "               (SELECT T.CAR_LICENSE\n" +
                "                  FROM FN_CUSTOMER_EXPAND T\n" +
                "                 WHERE T.ID = C.CUSTOMER_EXPAND_ID) CAR_LICENSE,\n" +
                "              (SELECT distinct M.partner_id  FROM retail_order_discount t,retail_order_item f,mas_cross_ic M\n" +
                "                                WHERE T.retail_item_id = f.ID and c.id= f.sale_id and t.crossIC_id = m.id ) crossIC_id,\n" +
                "               B.PAYMENT_METHOD_TYPE,'零售单' SOURCE_BUSINESS_TYPE,\n" +
                "               (SELECT t.NAME\n" +
                "                  FROM mas_payment_method t\n" +
                "                 WHERE T.ID = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_TYPE_NAME,\n" +
                "               'form/retail.order' FORM_ID,'retail.order.findOne' API,"+
                "               'e服务+' accountType\n" +
                "          FROM RETAIL_ORDER_PAYMENT_METHOD B,\n" +
                "               RETAIL_ORDER                C," +
                "               RETAIL_ORDER_ITEM E \n" +
                "         WHERE B.RETAIL_ORDER_ID = C.ID\n" +
                "           AND B.PAYMENT_METHOD_TYPE IN ('09')\n" +
                "           AND C.CLAIM_TYPE IS NULL\n" +
                "           AND (B.CUSTOMER_ID = 'C0005998' OR EXISTS(SELECT 1\n" +
                "             FROM MAS_PARTY_ROLE R\n" +
                "             WHERE R.PARTY_ID=(SELECT MPR.PARTY_ID FROM MAS_PARTY_ROLE MPR WHERE MPR.ID=B.CUSTOMER_ID) and R.ROLE_ID='Company'\n" +
                "              ))      AND ((SELECT t.Party_Id\n" +
                "                      FROM MAS_PARTY_ROLE t\n" +
                "                      WHERE T.ID = C.COMPANY_ID) !=\n" +
                "                     (SELECT t.Party_Id\n" +
                "                      FROM MAS_PARTY_ROLE t\n" +
                "                      WHERE T.ID = B.CUSTOMER_ID))" +
                "           AND E.PRODUCT_ID IN ('0002935068','0002902247','0002935072','0003020877','0002902250') " +
                "           AND E.SALE_ID = C.ID " +
                "        )\n" +
                "   WHERE 1=1 ");


        JSONArray DATE_OF_STATISTICS = param.getJSONArray("DATE_OF_STATISTICS");
        if (DATE_OF_STATISTICS!=null) {
            String DATE_OF_STATISTICS_S = DATE_OF_STATISTICS.getString(0);
            Date DATE_OF_STATISTICS_STATE = StringToDate(DATE_OF_STATISTICS_S,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_STATE_S = DateUtil.format(DATE_OF_STATISTICS_STATE,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_STATE",DATE_OF_STATISTICS_STATE_S);
            String DATE_OF_STATISTICS_E = DATE_OF_STATISTICS.getString(1);
            Date DATE_OF_STATISTICS_END = StringToDate(DATE_OF_STATISTICS_E,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_END_S = DateUtil.format(DATE_OF_STATISTICS_END,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_END",DATE_OF_STATISTICS_END_S);
        }
        if(param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_STATE") != null)  stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd')  ");
        if(param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_END") != null)  stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1 ");
        if(param.get("COMPANY_ID") != null ) stringBuffer.append(" AND COMPANY_ID = :COMPANY_ID ");
        if(param.get("DEPT_ID") != null )  stringBuffer.append(" AND DEPT_ID = :DEPT_ID ");
        if(param.get("ACCOUNTTYPE") != null )  stringBuffer.append(" AND ACCOUNTTYPE = :ACCOUNTTYPE ");
        if(param.get("DATE_APPROVE") != null)  stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_APPROVE,'yyyy-MM-dd') ");
        if(param.get("DATE_APPROVE") != null)  stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_APPROVE,'yyyy-MM-dd') +1");
        stringBuffer.append(" GROUP BY DEPT_ID,\n" +
                "          DEPT_NAME,\n" +
                "          COMPANY_ID,\n" +
                "          COMPANY_NAME,\n" +
                "          CUSTOMER_ID,\n" +
                "          CUSTOMER_NAME,\n" +
                "          TO_CHAR(DATE_APPROVE, 'yyyy-MM-dd'),\n" +
                "          SOURCE_ID,\n" +
                "          CAR_LICENSE," +
                "          SOURCE_BUSINESS_TYPE,\n" +
                "          CROSSIC_ID,\n" +
                "          PAYMENT_METHOD_TYPE_NAME,\n" +
                "          FORM_ID,API,"+
                "          ACCOUNTTYPE,\n" +
                "          PAYMENT_METHOD_TYPE ");

        return toJSONArray(find(stringBuffer.toString(), param));
    }

    /**
     * 查询应收账款(内)明细 e服务
     * @param param
     * @return
     */
    public JSONArray find_eService(JSONObject param){
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append(" SELECT F.PRICE_TOTAL AMOUNT, A.ORIGIN," +
                " (select name from soe_business_type b where b.id = A.ORIGIN_BUSINESS_TYPE) ORIGIN_BUSINESS_TYPE,\n" +
                "               A.DEPT_ID,\n" +
                "               (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID) DEPT_NAME,\n" +
                "               A.COMPANY_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = A.COMPANY_ID) COMPANY_NAME,\n" +
                "               A.PARTNER_ID CUSTOMER_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = A.PARTNER_ID) CUSTOMER_NAME,\n" +
                "              TO_CHAR(A.CREATE_DATE,'yyyy-MM-dd') DATE_APPROVE,\n" +
                "               A.ID,\n" +
                "               A.SOURCE,\n" +
                "             'form/purch.voucher' FORM_ID,'voucher.findOne' API," +
                "               (SELECT t.car_license\n" +
                "                  FROM FN_CUSTOMER_EXPAND t\n" +
                "                 WHERE t.id = a.customer_expand_id) car_license," +
                "               'e服务' ACCOUNTTYPE\n" +
                "          FROM ACC_VOUCHER a, ACC_VOUCHER_ITEM F\n" +
                "         WHERE A.ID = F.VOUCHER_ID\n" +
                "           AND A.VOUCHER_TYPE = 'CUSTOMER_SALE' " +
                "           AND F.PRODUCT_ID = '0002902284'" +
                "           AND a.ORIGIN_BUSINESS_TYPE<>'ProfitAndLoss'\n" +
                "           AND ((SELECT t.Party_Id\n" +
                "                   FROM MAS_PARTY_ROLE t\n" +
                "                  WHERE T.ID = A.COMPANY_ID) !=\n" +
                "               (SELECT t.Party_Id\n" +
                "                   FROM MAS_PARTY_ROLE t\n" +
                "                  WHERE T.ID = A.PARTNER_ID))" +
                " ");
        JSONArray DATE_OF_STATISTICS = param.getJSONArray("DATE_OF_STATISTICS");
        if (DATE_OF_STATISTICS!=null) {
            String DATE_OF_STATISTICS_S = DATE_OF_STATISTICS.getString(0);
            Date DATE_OF_STATISTICS_STATE = StringToDate(DATE_OF_STATISTICS_S,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_STATE_S = DateUtil.format(DATE_OF_STATISTICS_STATE,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_STATE",DATE_OF_STATISTICS_STATE_S);
            String DATE_OF_STATISTICS_E = DATE_OF_STATISTICS.getString(1);
            Date DATE_OF_STATISTICS_END = StringToDate(DATE_OF_STATISTICS_E,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_END_S = DateUtil.format(DATE_OF_STATISTICS_END,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_END",DATE_OF_STATISTICS_END_S);
        }

        if(param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_STATE") != null)  stringBuffer.append(" AND A.CREATE_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd')  ");
        if(param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_END") != null)  stringBuffer.append(" AND A.CREATE_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
        if(param.get("COMPANY_ID") != null ) stringBuffer.append(" AND COMPANY_ID = :COMPANY_ID ");
        if(param.get("DEPT_ID") != null )  stringBuffer.append(" AND DEPT_ID = :DEPT_ID ");
        if(param.get("DATE_APPROVE") != null)  stringBuffer.append(" AND A.CREATE_DATE >= to_date(:DATE_APPROVE,'yyyy-MM-dd') ");
        if(param.get("DATE_APPROVE") != null)  stringBuffer.append(" AND A.CREATE_DATE < to_date(:DATE_APPROVE,'yyyy-MM-dd') +1");
        return toJSONArray(find(stringBuffer.toString(), param));
    }
    /**
     * 接案介绍费
     * @param param
     * @return
     */
    public JSONArray find_receivableInMxByExpense(JSONObject param){
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("SELECT  TO_CHAR(A.CREATE_DATE,'yyyy-MM-dd') DATE_APPROVE,\n" +
                "        B.GET_CAR_DEPT,\n" +
                "                (SELECT NAME FROM MAS_DEPT WHERE ID = B.GET_CAR_DEPT) GET_CAR_DEPT_NAME,\n" +
                "                A.CAR_LICENSE,\n" +
                "               (SELECT DESCRIPTION FROM MAS_ENUMERATION  WHERE ID = A.CASE_TYPE) CASE_TYPE,\n" +
                "                B.SERVICE_DEPT_ID,\n" +
                "                (SELECT NAME FROM MAS_DEPT WHERE ID = B.SERVICE_DEPT_ID) SERVICE_DEPT_NAME,\n" +
                "                B.COMPANY_ID,\n" +
                "                (SELECT C.NAME\n" +
                "        FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "        WHERE C.ID = D.PARTY_ID\n" +
                "        AND D.ID =  B.COMPANY_ID) COMPANY_NAME,\n" +
                "                A.LOSS_QUOTATION_AMT, A.AMOUNT,\n" +
                "                A.SOURCE AS ID ,A.SOURCE_BUSINESS_TYPE,\n" +
                "                A.ORIGIN,A.ORIGIN_BUSINESS_TYPE,\n" +
                "               'form/retail.order' FORM_ID,'retail.order.findOne' API,"+
                "                '定损介绍费' ACCOUNTTYPE\n" +
                "        FROM FN_SETTLE_EXPENSE A , RETAIL_ORDER B\n" +
                "        WHERE A.SOURCE = B.ID\n" +
                "        and A.EXPENSE_TYPE = 'infoExpense'\n" +
                "        AND A.TYPE = 'in' " );

        JSONArray DATE_OF_STATISTICS = param.getJSONArray("DATE_OF_STATISTICS");
        if (DATE_OF_STATISTICS!=null) {
            String DATE_OF_STATISTICS_S = DATE_OF_STATISTICS.getString(0);
            Date DATE_OF_STATISTICS_STATE = StringToDate(DATE_OF_STATISTICS_S,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_STATE_S = DateUtil.format(DATE_OF_STATISTICS_STATE,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_STATE",DATE_OF_STATISTICS_STATE_S);
            String DATE_OF_STATISTICS_E = DATE_OF_STATISTICS.getString(1);
            Date DATE_OF_STATISTICS_END = StringToDate(DATE_OF_STATISTICS_E,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_END_S = DateUtil.format(DATE_OF_STATISTICS_END,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_END",DATE_OF_STATISTICS_END_S);
        }

        if(param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_STATE") != null)  stringBuffer.append(" AND A.CREATE_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd')  ");
        if(param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_END") != null)  stringBuffer.append(" AND A.CREATE_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
        if(param.get("COMPANY_ID") != null ) stringBuffer.append(" AND B.COMPANY_ID = :COMPANY_ID ");
        if(param.get("DEPT_ID") != null )  stringBuffer.append(" AND B.GET_CAR_DEPT = :DEPT_ID ");
        if(param.get("DATE_APPROVE") != null)  stringBuffer.append(" AND A.CREATE_DATE >= to_date(:DATE_APPROVE,'yyyy-MM-dd') ");
        if(param.get("DATE_APPROVE") != null)  stringBuffer.append(" AND A.CREATE_DATE < to_date(:DATE_APPROVE,'yyyy-MM-dd') + 1");
        return toJSONArray(find(stringBuffer.toString(), param));
    }

    /**
     * 平台内客户
     * @param param
     * @return
     */
    public JSONArray find_receivableInMxByVoucher(JSONObject param){
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("SELECT B.PRICE_TOTAL AMOUNT,\n" +
                "               A.DEPT_ID,\n" +
                        "               (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID) DEPT_NAME,\n" +
                        "               A.COMPANY_ID,\n" +
                        "               (SELECT C.NAME\n" +
                        "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                        "                 WHERE C.ID = D.PARTY_ID\n" +
                        "                   AND D.ID = A.COMPANY_ID) COMPANY_NAME,\n" +
                        "               A.PARTNER_ID CUSTOMER_ID,\n" +
                        "               (SELECT C.NAME\n" +
                        "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                        "                 WHERE C.ID = D.PARTY_ID\n" +
                        "                   AND D.ID = A.PARTNER_ID) CUSTOMER_NAME,\n" +
                        "              TO_CHAR(A.CREATE_DATE,'yyyy-MM-dd') DATE_APPROVE,\n" +
                        "               A.ID,\n" +
                        "               A.SOURCE,\n" +
                        "               'form/purch.voucher' FORM_ID,'voucher.findOne' API,"+
                        "               '平台内客户' ACCOUNTTYPE\n"
                +C_IN_PLATFORM_CLIENT+
                " ");
        JSONArray DATE_OF_STATISTICS = param.getJSONArray("DATE_OF_STATISTICS");
        if (DATE_OF_STATISTICS!=null) {
            String DATE_OF_STATISTICS_S = DATE_OF_STATISTICS.getString(0);
            Date DATE_OF_STATISTICS_STATE = StringToDate(DATE_OF_STATISTICS_S,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_STATE_S = DateUtil.format(DATE_OF_STATISTICS_STATE,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_STATE",DATE_OF_STATISTICS_STATE_S);
            String DATE_OF_STATISTICS_E = DATE_OF_STATISTICS.getString(1);
            Date DATE_OF_STATISTICS_END = StringToDate(DATE_OF_STATISTICS_E,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_END_S = DateUtil.format(DATE_OF_STATISTICS_END,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_END",DATE_OF_STATISTICS_END_S);
        }

        if(param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_STATE") != null)  stringBuffer.append(" AND A.CREATE_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd')  ");
        if(param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_END") != null)  stringBuffer.append(" AND A.CREATE_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
        if(param.get("COMPANY_ID") != null ) stringBuffer.append(" AND COMPANY_ID = :COMPANY_ID ");
        if(param.get("DEPT_ID") != null )  stringBuffer.append(" AND DEPT_ID = :DEPT_ID ");
        if(param.get("DATE_APPROVE") != null)  stringBuffer.append(" AND A.CREATE_DATE >= to_date(:DATE_APPROVE,'yyyy-MM-dd') ");
        if(param.get("DATE_APPROVE") != null)  stringBuffer.append(" AND A.CREATE_DATE < to_date(:DATE_APPROVE,'yyyy-MM-dd') +1");
        return toJSONArray(find(stringBuffer.toString(), param));
    }

    /**
     * 货币资金（不含定损代收）
     * @param param
     * @return
     */
    public JSONArray payment(JSONObject param) throws ParseException {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("SELECT \n" +
                "       COMPANY_ID,\n" +
                "       DEPT_ID,\n" +
                "       payment_method_type,\n" +
                "       NVL(SUM(AMOUNT + DS_AMT), 0)            AMT,\n" +
                "       NVL(SUM(YS_AMT), 0)                     YS_AMT,\n" +
                "       NVL(SUM(ZJSR_AMT), 0)                   ZJSR_AMT,\n" +
                "       NVL(SUM(AMOUNT - YS_AMT + ZJSR_AMT), 0) ZJ_AMT,\n" +
                "       NVL(SUM(AMOUNT - YS_AMT + ZJSR_AMT + DS_AMT), 0)\n" +
                "                                               AMOUNT,\n" +
                "       NVL(SUM(DS_AMT), 0)                     DS_AMT,\n" +
                "       DEPT_NAME,\n" +
                "       PAYMENT_METHOD_TYPE_NAME,\n" +
                "       COMPANY_NAME\n" +
                "FROM (SELECT c.date_approve,\n" +
                "             C.DEPT_ID,\n" +
                "             (SELECT NAME FROM MAS_DEPT WHERE ID = C.DEPT_ID)                             DEPT_NAME,\n" +
                "             b.payment_method_type,\n" +
                "             (SELECT t.NAME FROM mas_payment_method t WHERE T.ID = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_TYPE_NAME,\n" +
                "             C.COMPANY_ID,\n" +
                "             (SELECT C.NAME\n" +
                "              FROM MAS_PARTY C,\n" +
                "                   MAS_PARTY_ROLE D\n" +
                "              WHERE C.ID = D.PARTY_ID\n" +
                "                AND D.ID = C.COMPANY_ID)                                                  COMPANY_NAME,\n" +
                "             B.AMOUNT,\n" +
                "             0                                                                            DS_AMT,\n" +
                "             0                                                                            YS_AMT,\n" +
                "             0                                                                            ZJSR_AMT\n" +
                "      FROM RETAIL_ORDER_PAYMENT_METHOD B,\n" +
                "           RETAIL_ORDER C\n" +
                "      WHERE B.RETAIL_ORDER_ID = C.ID\n" +
                "        AND B.PAYMENT_METHOD_TYPE NOT IN ('04','10', '12',\n" +
                "                                          '05', '09', '003')\n" +
                "        AND C.CLAIM_TYPE IS NULL\n" +
                "      UNION ALL\n" +
                "      SELECT C.DATE_APPROVE,\n" +
                "             C.DEPT_ID,\n" +
                "             (SELECT NAME FROM MAS_DEPT WHERE ID = C.DEPT_ID)\n" +
                "                                         DEPT_NAME,\n" +
                "             b.payment_method_type,\n" +
                "             (SELECT t.NAME FROM mas_payment_method t WHERE T.ID = B.PAYMENT_METHOD_TYPE)\n" +
                "                                         PAYMENT_METHOD_TYPE_NAME,\n" +
                "             C.COMPANY_ID,\n" +
                "             (SELECT C.NAME\n" +
                "              FROM MAS_PARTY C,\n" +
                "                   MAS_PARTY_ROLE D\n" +
                "              WHERE C.ID = D.PARTY_ID\n" +
                "                AND D.ID = C.COMPANY_ID) COMPANY_NAME,\n" +
                "             B.AMOUNT,\n" +
                "             0                           DS_AMT,\n" +
                "             0                           YS_AMT,\n" +
                "             0                           ZJSR_AMT\n" +
                "      FROM ADVANCE_PAYMENT_METHOD B,\n" +
                "           ADVANCE_PAYMENT C\n" +
                "      WHERE B.payment_id = C.ID\n" +
                "        AND C.STATE = 'approve'\n" +
                "        AND B.PAYMENT_METHOD_TYPE NOT IN ('04','10', '12', '05', '09', '003')\n" +
                "      UNION ALL\n" +
                "      SELECT c.date_approve,\n" +
                "             C.DEPT_ID,\n" +
                "             (SELECT NAME FROM MAS_DEPT WHERE ID = C.DEPT_ID)                             DEPT_NAME,\n" +
                "             b.payment_method_type,\n" +
                "             (SELECT t.NAME FROM mas_payment_method t WHERE T.ID = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_TYPE_NAME,\n" +
                "             C.COMPANY_ID,\n" +
                "             (SELECT C.NAME\n" +
                "              FROM MAS_PARTY C,\n" +
                "                   MAS_PARTY_ROLE D\n" +
                "              WHERE C.ID = D.PARTY_ID\n" +
                "                AND D.ID = C.COMPANY_ID)                                                  COMPANY_NAME,\n" +
                "             0                                                                            AMOUNT,\n" +
                "             0                                                                            DS_AMT,\n" +
                "             B.AMOUNT                                                                     YS_AMT,\n" +
                "             0                                                                            ZJSR_AMT\n" +
                "      FROM RETAIL_ORDER_PAYMENT_METHOD B,\n" +
                "           RETAIL_ORDER C\n" +
                "      WHERE B.RETAIL_ORDER_ID = C.ID\n" +
                "        AND B.PAYMENT_METHOD_TYPE IN ('09')\n" +
                "        AND C.ORDER_TYPE\n" +
                "          NOT IN ('agencyPay', 'noDirectPay', 'directPay', 'DS0')\n" +
                "      UNION ALL\n" +
                "      SELECT C.DATE_APPROVE,\n" +
                "             C.DEPT_ID,\n" +
                "             (SELECT NAME FROM MAS_DEPT WHERE ID = C.DEPT_ID) DEPT_NAME,\n" +
                "             b.payment_method_type,\n" +
                "             (SELECT t.NAME\n" +
                "              FROM mas_payment_method t\n" +
                "              WHERE T.ID = B.PAYMENT_METHOD_TYPE)             PAYMENT_METHOD_TYPE_NAME,\n" +
                "             C.COMPANY_ID,\n" +
                "             (SELECT C.NAME\n" +
                "              FROM MAS_PARTY C,\n" +
                "                   MAS_PARTY_ROLE D\n" +
                "              WHERE C.ID = D.PARTY_ID\n" +
                "                AND D.ID = C.COMPANY_ID)                      COMPANY_NAME,\n" +
                "             0                                                AMOUNT,\n" +
                "             0                                                DS_AMT,\n" +
                "             B.AMOUNT                                         YS_AMT,\n" +
                "             0                                                ZJSR_AMT\n" +
                "      FROM ADVANCE_PAYMENT_METHOD B,\n" +
                "           ADVANCE_PAYMENT C\n" +
                "      WHERE B.payment_id = C.ID\n" +
                "        AND C.STATE = 'approve'\n" +
                "        AND B.PAYMENT_METHOD_TYPE\n" +
                "          IN ('09')\n" +
                "      UNION ALL\n" +
                "      SELECT A.Create_Date          DATE_APPROVE,\n" +
                "             A.DEPT_ID,\n" +
                "             (SELECT NAME\n" +
                "              FROM MAS_DEPT\n" +
                "              WHERE ID = A.DEPT_ID) DEPT_NAME,\n" +
                "             ''                     PAYMENT_METHOD_TYPE,\n" +
                "             ''                     PAYMENT_METHOD_TYPE_NAME,\n" +
                "             A.COMPANY_ID,\n" +
                "             (SELECT C.NAME\n" +
                "              FROM MAS_PARTY C,\n" +
                "                   MAS_PARTY_ROLE D\n" +
                "              WHERE C.ID = D.PARTY_ID\n" +
                "                AND D.ID = A.COMPANY_ID)\n" +
                "                                    COMPANY_NAME,\n" +
                "             0                      AMOUNT,\n" +
                "             0                      DS_AMT,\n" +
                "             B.PRICE_TOTAL          YS_AMT,\n" +
                "             0                      ZJSR_AMT\n" +
                "      FROM ACC_VOUCHER a,\n" +
                "           ACC_VOUCHER_ITEM B\n" +
                "      WHERE A.ID = B.VOUCHER_ID\n" +
                "        AND A.VOUCHER_TYPE = 'CUSTOMER_SALE'\n" +
                "        AND a.ORIGIN_BUSINESS_TYPE <> 'ProfitAndLoss'\n" +
                "        AND ((SELECT t.Party_Id FROM MAS_PARTY_ROLE t WHERE T.ID = A.COMPANY_ID) != (SELECT t.Party_Id\n" +
                "                                                                                     FROM MAS_PARTY_ROLE t\n" +
                "                                                                                     WHERE T.ID = A.PARTNER_ID))\n" +
                "      UNION ALL\n" +
                "      SELECT A.Create_Date                                    DATE_APPROVE,\n" +
                "             A.DEPT_ID,\n" +
                "             (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID) DEPT_NAME,\n" +
                "             ''                                               PAYMENT_METHOD_TYPE,\n" +
                "             ''                                               PAYMENT_METHOD_TYPE_NAME,\n" +
                "             A.PAYMENT_ID                                     COMPANY_ID,\n" +
                "             (SELECT C.NAME\n" +
                "              FROM MAS_PARTY C,\n" +
                "                   MAS_PARTY_ROLE D\n" +
                "              WHERE C.ID = D.PARTY_ID\n" +
                "                AND D.ID = A.PAYMENT_ID)                      COMPANY_NAME,\n" +
                "             0                                                AMOUNT,\n" +
                "             0                                                DS_AMT,\n" +
                "             A.AMOUNT\n" +
                "                                                              YS_AMT,\n" +
                "             0                                                ZJSR_AMT\n" +
                "      FROM FN_SETTLE_EXPENSE A\n" +
                "      WHERE A.EXPENSE_TYPE = 'infoExpense'\n" +
                "        AND A.TYPE\n" +
                "          = 'in'\n" +
                "      UNION ALL\n" +
                "      SELECT C.APPROVE_DATE         DATE_APPROVE,\n" +
                "             C.DEPT_ID,\n" +
                "             (SELECT NAME\n" +
                "              FROM MAS_DEPT\n" +
                "              WHERE ID = C.DEPT_ID) DEPT_NAME,\n" +
                "             E.PAYMENT_METHOD_TYPE,\n" +
                "             M.name                 PAYMENT_METHOD_TYPE_NAME,\n" +
                "             C.COMPANY_ID,\n" +
                "             (SELECT C.NAME\n" +
                "              FROM MAS_PARTY C,\n" +
                "                   MAS_PARTY_ROLE D\n" +
                "              WHERE C.ID = D.PARTY_ID\n" +
                "                AND D.ID = C.COMPANY_ID)\n" +
                "                                    COMPANY_NAME,\n" +
                "             0                      AMOUNT,\n" +
                "             0                      DS_AMT,\n" +
                "             0                      YS_AMT,\n" +
                "             E.INCOME_AMOUNT        ZJSR_AMT\n" +
                "      from (SELECT A.ID DEPT_ID,\n" +
                "                   B.ID IMCOME_ID,\n" +
                "                   B.APPROVE_DATE,\n" +
                "                   B.COMPANY_ID\n" +
                "            FROM MAS_DEPT A,\n" +
                "                 MONEY_INCOME B\n" +
                "            WHERE A.id\n" +
                "                = B.DEPT_ID\n" +
                "              AND A.PROFIT_CENTER_ID = B.PROFIT_CENTER_ID\n" +
                "              AND B.STATE = 'approve') C,\n" +
                "           MONEY_INCOME_ITEM E,\n" +
                "           Mas_Payment_Method M\n" +
                "      WHERE C.IMCOME_ID = E.MONEY_INCOME\n" +
                "        AND E.PAYMENT_METHOD_TYPE = M.ID" +
                "       AND M.ID <>'05' \n" +
                "      UNION ALL\n" +
                "      SELECT c.date_approve,\n" +
                "             C.DEPT_ID,\n" +
                "             (SELECT NAME FROM MAS_DEPT WHERE ID = C.DEPT_ID)\n" +
                "                                         DEPT_NAME,\n" +
                "             b.payment_method_type,\n" +
                "             (SELECT t.NAME FROM mas_payment_method t WHERE T.ID = B.PAYMENT_METHOD_TYPE)\n" +
                "                                         PAYMENT_METHOD_TYPE_NAME,\n" +
                "             C.COMPANY_ID,\n" +
                "             (SELECT C.NAME\n" +
                "              FROM MAS_PARTY C,\n" +
                "                   MAS_PARTY_ROLE D\n" +
                "              WHERE C.ID = D.PARTY_ID\n" +
                "                AND D.ID = C.COMPANY_ID) COMPANY_NAME,\n" +
                "             0                           AMOUNT,\n" +
                "             B.AMOUNT                    DS_AMT,\n" +
                "             0                           YS_AMT,\n" +
                "             0                           ZJSR_AMT\n" +
                "      FROM RETAIL_ORDER_PAYMENT_METHOD B,\n" +
                "           RETAIL_ORDER C\n" +
                "      WHERE B.RETAIL_ORDER_ID = C.ID\n" +
                "        AND B.PAYMENT_METHOD_TYPE NOT IN ('04','10', '12', '05', '09', '003')\n" +
                "         AND C.CLAIM_TYPE IS not NULL)\n" +
                "WHERE 1 = 1\n" +
                "  and PAYMENT_METHOD_TYPE NOT IN ('09')");

        JSONArray DATE_OF_STATISTICS = param.getJSONArray("DATE_OF_STATISTICS");
        if (DATE_OF_STATISTICS!=null) {
            String DATE_OF_STATISTICS_S = DATE_OF_STATISTICS.getString(0);
            Date DATE_OF_STATISTICS_STATE = StringToDate(DATE_OF_STATISTICS_S,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_STATE_S = DateUtil.format(DATE_OF_STATISTICS_STATE,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_STATE",DATE_OF_STATISTICS_STATE_S);
            String DATE_OF_STATISTICS_E = DATE_OF_STATISTICS.getString(1);
            Date DATE_OF_STATISTICS_END = StringToDate(DATE_OF_STATISTICS_E,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_END_S = DateUtil.format(DATE_OF_STATISTICS_END,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_END",DATE_OF_STATISTICS_END_S);
        }
        DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        if(param.get("DATE_OF_STATISTICS_STATE") != null)  stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd')  ");
        if(param.get("DATE_OF_STATISTICS_END") != null)  stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
        if(param.get("COMPANY_ID") != null ) stringBuffer.append(" AND COMPANY_ID = :COMPANY_ID ");
        if(param.get("DEPT_ID") != null )  stringBuffer.append(" AND DEPT_ID = :DEPT_ID ");
        if(param.get("DATE_APPROVE") != null) {
            try {
                Date date = formatter.parse(param.getString("DATE_APPROVE"));
                stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_APPROVE,'yyyy-MM-dd') ");
                stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_APPROVE,'yyyy-MM-dd') +1");
            } catch (Exception e) {
                Date start = formatter.parse(param.getString("DATE_OF_STATISTICS_STATE"));
                Date end = formatter.parse(param.getString("DATE_OF_STATISTICS_END"));
                LocalDate localDateStart = DateUtil.UDateToLocalDate(start);
                LocalDate localDateEnd = DateUtil.UDateToLocalDate(end);
                if (localDateStart.getMonthValue() == localDateEnd.getMonthValue()) {
                    stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd') ");
                    stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
                } else {
                    formatter = new SimpleDateFormat("yyyy-MM");
                    Date dateApprove = formatter.parse(param.getString("DATE_APPROVE"));
                    LocalDate loDateApprove = DateUtil.UDateToLocalDate(dateApprove);
                    if (localDateStart.getMonthValue() == loDateApprove.getMonthValue()){
                        //获取月末
                        LocalDate monthOfLastDate = loDateApprove.with(TemporalAdjusters.lastDayOfMonth());
                        String SmonthOfLastDate = monthOfLastDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                        param.put("SmonthOfLastDate",SmonthOfLastDate);
                        stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd') ");
                        stringBuffer.append(" AND DATE_APPROVE < to_date(:SmonthOfLastDate,'yyyy-MM-dd') + 1");
                    } else {
                        //获取月头
                        LocalDate monthOfFirstDate = loDateApprove.with(TemporalAdjusters.firstDayOfMonth());
                        LocalDate monthOfLastDate = loDateApprove.with(TemporalAdjusters.lastDayOfMonth());
                        String SmonthOfFirstDate = monthOfFirstDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                        String monthOfLastDateStr = monthOfLastDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                        param.put("SmonthOfFirstDate",SmonthOfFirstDate);
                        param.put("SmonthOfLastDate",monthOfLastDateStr);
                        stringBuffer.append(" AND DATE_APPROVE >= to_date(:SmonthOfFirstDate,'yyyy-MM-dd') ");
                        stringBuffer.append(" AND DATE_APPROVE < to_date(:SmonthOfLastDate,'yyyy-MM-dd') + 1");
                    }
                }

            }
        }
        stringBuffer.append(" GROUP BY PAYMENT_METHOD_TYPE,DEPT_NAME,COMPANY_ID,DEPT_ID,PAYMENT_METHOD_TYPE_NAME, COMPANY_NAME");
        stringBuffer.append(" ORDER BY PAYMENT_METHOD_TYPE");

        return toJSONArray(find(stringBuffer.toString(), param));
    }


    /**
     * 货币资金（不含定损代收）
     * @param param
     * @return
     */
    public JSONArray paymentMx(JSONObject param) throws ParseException {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append(" SELECT TO_CHAR(DATE_APPROVE,'yyyy-MM-dd') DATE_APPROVE,COMPANY_ID,DEPT_ID,payment_method_type,\n" +
                "       NVL(SUM(AMOUNT + DS_AMT),0) AMT,\n" +
                "       NVL(SUM(YS_AMT),0) YS_AMT,\n" +
                "       NVL(SUM(ZJSR_AMT),0) ZJSR_AMT,\n" +
                "        NVL(SUM(AMOUNT -YS_AMT + ZJSR_AMT),0) ZJ_AMT,\n" +
                "        NVL(SUM(AMOUNT -YS_AMT + ZJSR_AMT +DS_AMT), 0) AMOUNT,\n" +
                "        NVL(SUM(DS_AMT), 0) DS_AMT,\n" +
                "        DEPT_NAME,PAYMENT_METHOD_TYPE_NAME, COMPANY_NAME\n" +
                "  FROM (SELECT c.date_approve,\n" +
                "               C.DEPT_ID,\n" +
                "               (SELECT NAME FROM MAS_DEPT WHERE ID = C.DEPT_ID) DEPT_NAME,\n" +
                "               b.payment_method_type,\n" +
                "               (SELECT t.NAME\n" +
                "                  FROM mas_payment_method t\n" +
                "                 WHERE T.ID = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_TYPE_NAME,\n" +
                "               C.COMPANY_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = C.COMPANY_ID) COMPANY_NAME,\n" +
                "               B.AMOUNT,\n" +
                "               0 DS_AMT,\n" +
                "               0 YS_AMT,\n" +
                "               0 ZJSR_AMT\n" +
                "          FROM RETAIL_ORDER_PAYMENT_METHOD B, RETAIL_ORDER C\n" +
                "         WHERE B.RETAIL_ORDER_ID = C.ID\n" +
//                "           AND C.STATE IN ('done', 'confirmed')\n" +
                "           AND C.CLAIM_TYPE IS NULL AND PAYMENT_METHOD_TYPE not in(05,10,12) \n" +
                "      UNION ALL\n" );
        stringBuffer.append("        SELECT C.DATE_APPROVE,\n" +
                "               C.DEPT_ID,\n" +
                "               (SELECT NAME FROM MAS_DEPT WHERE ID = C.DEPT_ID) DEPT_NAME,\n" +
                "               b.payment_method_type,\n" +
                "               (SELECT t.NAME\n" +
                "                  FROM mas_payment_method t\n" +
                "                 WHERE T.ID = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_TYPE_NAME,\n" +
                "               C.COMPANY_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = C.COMPANY_ID) COMPANY_NAME,\n" +
                "               B.AMOUNT,\n" +
                "               0 DS_AMT,\n" +
                "               0 YS_AMT,\n" +
                "               0 ZJSR_AMT\n" +
                "          FROM ADVANCE_PAYMENT_METHOD B, ADVANCE_PAYMENT C\n" +
                "         WHERE B.payment_id = C.ID\n" +
                "           AND C.STATE = 'approve'\n" +
                "           AND B.PAYMENT_METHOD_TYPE NOT IN ('04','10', '12', '05', '09','003')\n" +
                "     UNION ALL\n" );

        stringBuffer.append("        SELECT c.date_approve,\n" +
                "               C.DEPT_ID,\n" +
                "               (SELECT NAME FROM MAS_DEPT WHERE ID = C.DEPT_ID) DEPT_NAME,\n" +
                "               b.payment_method_type,\n" +
                "               (SELECT t.NAME\n" +
                "                  FROM mas_payment_method t\n" +
                "                 WHERE T.ID = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_TYPE_NAME,\n" +
                "               C.COMPANY_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = C.COMPANY_ID) COMPANY_NAME,\n" +
                "               0 AMOUNT,\n" +
                "               0 DS_AMT,\n" +
                "                B.AMOUNT YS_AMT,\n" +
                "                0 ZJSR_AMT\n" +
                "          FROM RETAIL_ORDER_PAYMENT_METHOD B, RETAIL_ORDER C\n" +
                "         WHERE B.RETAIL_ORDER_ID = C.ID\n" +
//                "           AND C.STATE IN ('done', 'confirmed')\n" +
                "           AND B.PAYMENT_METHOD_TYPE IN ('09')\n" +
                "           AND C.ORDER_TYPE NOT IN\n" +
                "               ('agencyPay', 'noDirectPay', 'directPay', 'DS0')\n" +
                "        UNION ALL\n" );
        stringBuffer.append("        SELECT C.DATE_APPROVE,\n" +
                "               C.DEPT_ID,\n" +
                "               (SELECT NAME FROM MAS_DEPT WHERE ID = C.DEPT_ID) DEPT_NAME,\n" +
                "               b.payment_method_type,\n" +
                "               (SELECT t.NAME\n" +
                "                  FROM mas_payment_method t\n" +
                "                 WHERE T.ID = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_TYPE_NAME,\n" +
                "               C.COMPANY_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = C.COMPANY_ID) COMPANY_NAME,\n" +
                "               0 AMOUNT,\n" +
                "               0 DS_AMT,\n" +
                "               B.AMOUNT YS_AMT,\n" +
                "               0 ZJSR_AMT\n" +
                "          FROM ADVANCE_PAYMENT_METHOD B, ADVANCE_PAYMENT C\n" +
                "         WHERE B.payment_id = C.ID\n" +
                "           AND C.STATE = 'approve'\n" +
                "           AND B.PAYMENT_METHOD_TYPE IN ('09')\n" +
                "       UNION ALL\n" );
        stringBuffer.append("        SELECT A.Create_Date DATE_APPROVE,\n" +
                "               A.DEPT_ID,\n" +
                "               (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID) DEPT_NAME,\n" +
                "               '' PAYMENT_METHOD_TYPE,\n" +
                "               '' PAYMENT_METHOD_TYPE_NAME,\n" +
                "               A.COMPANY_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = A.COMPANY_ID) COMPANY_NAME,\n" +
                "               0 AMOUNT,\n" +
                "               0 DS_AMT,\n" +
                "               B.PRICE_TOTAL YS_AMT,\n" +
                "              0 ZJSR_AMT\n" +
                "          FROM ACC_VOUCHER a, ACC_VOUCHER_ITEM B\n" +
                "         WHERE A.ID = B.VOUCHER_ID\n" +
                "           AND A.VOUCHER_TYPE = 'CUSTOMER_SALE' " +
                "           AND a.ORIGIN_BUSINESS_TYPE<>'ProfitAndLoss' \n" +
                "           AND ((SELECT t.Party_Id\n" +
                "                   FROM MAS_PARTY_ROLE t\n" +
                "                  WHERE T.ID = A.COMPANY_ID) !=\n" +
                "               (SELECT t.Party_Id\n" +
                "                   FROM MAS_PARTY_ROLE t\n" +
                "                  WHERE T.ID = A.PARTNER_ID))\n" +
                " UNION ALL\n" );
        stringBuffer.append(
                "        SELECT A.Create_Date DATE_APPROVE,\n" +
                        "               A.DEPT_ID,\n" +
                        "               (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID) DEPT_NAME,\n" +
                        "               '' PAYMENT_METHOD_TYPE,\n" +
                        "               '' PAYMENT_METHOD_TYPE_NAME,\n" +
                        "               A.PAYMENT_ID COMPANY_ID,\n" +
                        "               (SELECT C.NAME\n" +
                        "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                        "                 WHERE C.ID = D.PARTY_ID\n" +
                        "                   AND D.ID = A.PAYMENT_ID) COMPANY_NAME,\n" +
                        "               0 AMOUNT,\n" +
                        "               0 DS_AMT,\n" +
                        "               A.AMOUNT YS_AMT,\n" +
                        "               0 ZJSR_AMT\n" +
                        "          FROM FN_SETTLE_EXPENSE A\n" +
                        "         WHERE A.EXPENSE_TYPE = 'infoExpense'\n" +
                        "           AND A.TYPE = 'in'\n" +
                        " UNION ALL \n" );
        stringBuffer.append(
                " SELECT C.APPROVE_DATE DATE_APPROVE,\n" +
                        "           C.DEPT_ID,\n" +
                        "               (SELECT NAME FROM MAS_DEPT WHERE ID = C.DEPT_ID) DEPT_NAME,\n" +
                        "               E.PAYMENT_METHOD_TYPE,\n" +
                        "               M.name PAYMENT_METHOD_TYPE_NAME,\n" +
                        "               C.COMPANY_ID,\n" +
                        "               (SELECT C.NAME\n" +
                        "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                        "                 WHERE C.ID = D.PARTY_ID\n" +
                        "                   AND D.ID = C.COMPANY_ID) COMPANY_NAME,\n" +
                        "               0 AMOUNT,\n" +
                        "               0 DS_AMT,\n" +
                        "               0 YS_AMT,\n" +
                        "               E.INCOME_AMOUNT ZJSR_AMT\n" +
                        "        from (SELECT A.ID DEPT_ID, B.ID IMCOME_ID, B.APPROVE_DATE, B.COMPANY_ID\n" +
                        "              FROM MAS_DEPT A, MONEY_INCOME B\n" +
                        "             WHERE A.id = B.DEPT_ID\n" +
                        "               AND A.PROFIT_CENTER_ID = B.PROFIT_CENTER_ID\n" +
                        "               AND B.STATE = 'approve') C, MONEY_INCOME_ITEM E,Mas_Payment_Method M\n" +
                        "     WHERE C.IMCOME_ID = E.MONEY_INCOME AND E.PAYMENT_METHOD_TYPE = M.ID \n" +
                        "   UNION ALL\n" +
                        "        SELECT c.date_approve,\n" +
                        "               C.DEPT_ID,\n" +
                        "               (SELECT NAME FROM MAS_DEPT WHERE ID = C.DEPT_ID) DEPT_NAME,\n" +
                        "               b.payment_method_type,\n" +
                        "               (SELECT t.NAME\n" +
                        "                  FROM mas_payment_method t\n" +
                        "                 WHERE T.ID = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_TYPE_NAME,\n" +
                        "               C.COMPANY_ID,\n" +
                        "               (SELECT C.NAME\n" +
                        "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                        "                 WHERE C.ID = D.PARTY_ID\n" +
                        "                   AND D.ID = C.COMPANY_ID) COMPANY_NAME,\n" +
                        "               0 AMOUNT,\n" +
                        "               B.AMOUNT DS_AMT,\n" +
                        "               0 YS_AMT,\n" +
                        "               0 ZJSR_AMT\n" +
                        "          FROM RETAIL_ORDER_PAYMENT_METHOD B, RETAIL_ORDER C\n" +
                        "         WHERE B.RETAIL_ORDER_ID = C.ID\n" +
                        "           AND B.PAYMENT_METHOD_TYPE NOT IN ('04','10', '12', '05', '09','003')\n" +
                        "            AND C.CLAIM_TYPE IS not NULL)\n" +
                        "               WHERE 1=1 and  PAYMENT_METHOD_TYPE NOT IN ('09') ");

        JSONArray DATE_OF_STATISTICS = param.getJSONArray("DATE_OF_STATISTICS");
        try {
            JSONArray DATE_APPROVE_S = param.getJSONArray("DATE_APPROVE");
            if (DATE_APPROVE_S.size() == 2) {
                DATE_OF_STATISTICS = DATE_APPROVE_S;
                param.put("DATE_APPROVE", null);
            }
        } catch (Exception e) {

        }

        if (DATE_OF_STATISTICS!=null) {
            String DATE_OF_STATISTICS_S = DATE_OF_STATISTICS.getString(0);
            Date DATE_OF_STATISTICS_STATE = StringToDate(DATE_OF_STATISTICS_S,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_STATE_S = DateUtil.format(DATE_OF_STATISTICS_STATE,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_STATE",DATE_OF_STATISTICS_STATE_S);
            String DATE_OF_STATISTICS_E = DATE_OF_STATISTICS.getString(1);
            Date DATE_OF_STATISTICS_END = StringToDate(DATE_OF_STATISTICS_E,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_END_S = DateUtil.format(DATE_OF_STATISTICS_END,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_END",DATE_OF_STATISTICS_END_S);
        }
        DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        if(param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_STATE") != null)  stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd')  ");
        if(param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_END") != null)  stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
        if(param.get("COMPANY_ID") != null ) stringBuffer.append(" AND COMPANY_ID = :COMPANY_ID ");
        if(param.get("DEPT_ID") != null )  stringBuffer.append(" AND DEPT_ID = :DEPT_ID ");
        if(param.get("PAYMENT_METHOD_TYPE") != null)  stringBuffer.append(" AND PAYMENT_METHOD_TYPE  = :PAYMENT_METHOD_TYPE ");
        if(param.get("DATE_APPROVE") != null) {
            try {
                Date date = formatter.parse(param.getString("DATE_APPROVE"));
                stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_APPROVE,'yyyy-MM-dd') ");
                stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_APPROVE,'yyyy-MM-dd') +1");
            } catch (Exception e) {
                if (!param.containsKey("DATE_OF_STATISTICS_STATE")){
                    formatter = new SimpleDateFormat("yyyy-MM");
                    Date dateApprove = formatter.parse(param.getString("DATE_APPROVE"));
                    LocalDate loDateApprove = DateUtil.UDateToLocalDate(dateApprove);
                    LocalDate monthOfFirstDate = loDateApprove.with(TemporalAdjusters.firstDayOfMonth());
                    LocalDate monthOfLastDate = loDateApprove.with(TemporalAdjusters.lastDayOfMonth());
                    String SmonthOfFirstDate = monthOfFirstDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                    String monthOfLastDateStr = monthOfLastDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                    param.put("SmonthOfFirstDate",SmonthOfFirstDate);
                    param.put("DATE_OF_STATISTICS_END",monthOfLastDateStr);
                    stringBuffer.append(" AND DATE_APPROVE >= to_date(:SmonthOfFirstDate,'yyyy-MM-dd') ");
                    stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");

                }else {
                    Date start = formatter.parse(param.getString("DATE_OF_STATISTICS_STATE"));
                    Date end = formatter.parse(param.getString("DATE_OF_STATISTICS_END"));
                    LocalDate localDateStart = DateUtil.UDateToLocalDate(start);
                    LocalDate localDateEnd = DateUtil.UDateToLocalDate(end);
                    if (localDateStart.getMonthValue() == localDateEnd.getMonthValue()) {
                        stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd') ");
                        stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
                    } else {
                        formatter = new SimpleDateFormat("yyyy-MM");
                        Date dateApprove = formatter.parse(param.getString("DATE_APPROVE"));
                        LocalDate loDateApprove = DateUtil.UDateToLocalDate(dateApprove);
                        if (localDateStart.getMonthValue() == loDateApprove.getMonthValue()){
                            //获取月末
                            LocalDate monthOfLastDate = loDateApprove.with(TemporalAdjusters.lastDayOfMonth());
                            String SmonthOfLastDate = monthOfLastDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                            param.put("SmonthOfLastDate",SmonthOfLastDate);
                            stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd') ");
                            stringBuffer.append(" AND DATE_APPROVE < to_date(:SmonthOfLastDate,'yyyy-MM-dd') + 1");
                        } else {
                            //获取月头
                            LocalDate monthOfFirstDate = loDateApprove.with(TemporalAdjusters.firstDayOfMonth());
                            String SmonthOfFirstDate = monthOfFirstDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                            param.put("SmonthOfFirstDate",SmonthOfFirstDate);
                            stringBuffer.append(" AND DATE_APPROVE >= to_date(:SmonthOfFirstDate,'yyyy-MM-dd') ");
                            stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
                        }
                    }
                }
            }
        }

        stringBuffer.append(" GROUP BY TO_CHAR(DATE_APPROVE,'yyyy-MM-dd'),DEPT_NAME,COMPANY_ID,DEPT_ID,payment_method_type,PAYMENT_METHOD_TYPE_NAME, COMPANY_NAME");
        stringBuffer.append(" order BY DATE_APPROVE");

        return toJSONArray(find(stringBuffer.toString(), param));
    }


    /**
     * 货币资金（不含定损代收）
     * @param param
     * @return
     */
    public JSONArray paymentMxGdj(JSONObject param){
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append(" SELECT TO_CHAR(DATE_APPROVE, 'yyyy-MM-dd') DATE_APPROVE,\n" +
                "       NVL(SUM(AMOUNT), 0) AMT,\n" +
                "       NVL(SUM(YS_AMT), 0) YS_AMT,\n" +
                "       NVL(SUM(ZJSR_AMT), 0) ZJSR_AMT,\n" +
                "       NVL(SUM(AMOUNT - YS_AMT + ZJSR_AMT + DS_AMT), 0) ZJ_AMT,\n" +
                "       NVL(SUM(AMOUNT - YS_AMT + ZJSR_AMT), 0) AMOUNT,\n" +
                "       NVL(SUM(DS_AMT), 0) DS_AMT,\n" +
                "       DEPT_NAME,\n" +
                "       RETAIL_ID,\n" +
                "       (GET_ENCRYPT_MOBILE(MOBILE)) AS MOBILE,\n" +
                "       CAR_LICENSE,\n" +
                "       PAYMENT_METHOD_TYPE_NAME,\n" +
                "       COMPANY_NAME\n" +
                "  FROM (SELECT c.date_approve,\n" +
                "               C.DEPT_ID,\n" +
                "               (SELECT NAME FROM MAS_DEPT WHERE ID = C.DEPT_ID) DEPT_NAME,\n" +
                "               C.ID RETAIL_ID,\n" +
                "               (SELECT (GET_ENCRYPT_MOBILE(T.MOBILE)) AS MOBILE\n" +
                "                       FROM FN_CUSTOMER_EXPAND T\n" +
                "                          WHERE T.ID = C.CUSTOMER_EXPAND_ID) MOBILE,\n" +
                "               (SELECT T.CAR_LICENSE\n" +
                "                 FROM FN_CUSTOMER_EXPAND T\n" +
                "                WHERE T.ID = C.CUSTOMER_EXPAND_ID) CAR_LICENSE, \n" +
                "               b.payment_method_type,\n" +
                "               (SELECT t.NAME\n" +
                "                  FROM mas_payment_method t\n" +
                "                 WHERE T.ID = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_TYPE_NAME,\n" +
                "               C.COMPANY_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = C.COMPANY_ID) COMPANY_NAME,\n" +
                "               B.AMOUNT,\n" +
                "               0 DS_AMT,\n" +
                "               0 YS_AMT,\n" +
                "               0 ZJSR_AMT\n" +
                "          FROM RETAIL_ORDER_PAYMENT_METHOD B, RETAIL_ORDER C\n" +
                "         WHERE B.RETAIL_ORDER_ID = C.ID\n" +
//                "           AND C.STATE IN ('done', 'confirmed')\n" +
                "           AND B.PAYMENT_METHOD_TYPE NOT IN ('04','10', '12', '05', '09')\n" +
                "           AND C.ORDER_TYPE NOT IN\n" +
                "               ('agencyPay', 'noDirectPay', 'directPay', 'DS0')\n" );
        stringBuffer.append( "        UNION ALL\n" +
                "        SELECT C.DATE_APPROVE,\n" +
                "               C.DEPT_ID,\n" +
                "               (SELECT NAME FROM MAS_DEPT WHERE ID = C.DEPT_ID) DEPT_NAME,\n" +
                "               C.ID RETAIL_ID,\n" +
                "               (SELECT (GET_ENCRYPT_MOBILE(T.MOBILE)) AS MOBILE\n" +
                "                       FROM FN_CUSTOMER_EXPAND T\n" +
                "                          WHERE T.ID = C.CUSTOMER_EXPAND_ID) MOBILE,\n" +
                "               (SELECT T.CAR_LICENSE\n" +
                "                 FROM FN_CUSTOMER_EXPAND T\n" +
                "                WHERE T.ID = C.CUSTOMER_EXPAND_ID) CAR_LICENSE, \n" +
                "               b.payment_method_type,\n" +
                "               (SELECT t.NAME\n" +
                "                  FROM mas_payment_method t\n" +
                "                 WHERE T.ID = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_TYPE_NAME,\n" +
                "               C.COMPANY_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = C.COMPANY_ID) COMPANY_NAME,\n" +
                "               B.AMOUNT,\n" +
                "               0 DS_AMT,\n" +
                "               0 YS_AMT,\n" +
                "               0 ZJSR_AMT\n" +
                "          FROM ADVANCE_PAYMENT_METHOD B, ADVANCE_PAYMENT C\n" +
                "         WHERE B.payment_id = C.ID\n" +
                "           AND C.STATE = 'approve'\n" +
                "           AND B.PAYMENT_METHOD_TYPE IN ('10', '12', '05', '09')\n" );
        stringBuffer.append("        UNION ALL\n" +
                "        SELECT c.date_approve,\n" +
                "               C.DEPT_ID,\n" +
                "               (SELECT NAME FROM MAS_DEPT WHERE ID = C.DEPT_ID) DEPT_NAME,\n" +
                "                C.ID RETAIL_ID,\n" +
                "               (SELECT (GET_ENCRYPT_MOBILE(T.MOBILE)) AS MOBILE\n" +
                "                       FROM FN_CUSTOMER_EXPAND T\n" +
                "                          WHERE T.ID = C.CUSTOMER_EXPAND_ID) MOBILE,\n" +
                "               (SELECT T.CAR_LICENSE\n" +
                "                 FROM FN_CUSTOMER_EXPAND T\n" +
                "                WHERE T.ID = C.CUSTOMER_EXPAND_ID) CAR_LICENSE, \n" +
                "               b.payment_method_type,\n" +
                "               (SELECT t.NAME\n" +
                "                  FROM mas_payment_method t\n" +
                "                 WHERE T.ID = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_TYPE_NAME,\n" +
                "               C.COMPANY_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = C.COMPANY_ID) COMPANY_NAME,\n" +
                "               0 AMOUNT,\n" +
                "               0 DS_AMT,\n" +
                "               B.AMOUNT YS_AMT,\n" +
                "               0 ZJSR_AMT\n" +
                "          FROM RETAIL_ORDER_PAYMENT_METHOD B, RETAIL_ORDER C\n" +
                "         WHERE B.RETAIL_ORDER_ID = C.ID\n" +
//                "           AND C.STATE IN ('done', 'confirmed')\n" +
                "           AND B.PAYMENT_METHOD_TYPE IN ('09')\n" +
                "           AND C.ORDER_TYPE NOT IN\n" +
                "               ('agencyPay', 'noDirectPay', 'directPay', 'DS0')\n" );
        stringBuffer.append("        UNION ALL\n" +
                "        SELECT C.DATE_APPROVE,\n" +
                "               C.DEPT_ID,\n" +
                "               (SELECT NAME FROM MAS_DEPT WHERE ID = C.DEPT_ID) DEPT_NAME,\n" +
                "                C.ID RETAIL_ID,\n" +
                "               (SELECT (GET_ENCRYPT_MOBILE(T.MOBILE)) AS MOBILE\n" +
                "                       FROM FN_CUSTOMER_EXPAND T\n" +
                "                          WHERE T.ID = C.CUSTOMER_EXPAND_ID) MOBILE,\n" +
                "               (SELECT T.CAR_LICENSE\n" +
                "                 FROM FN_CUSTOMER_EXPAND T\n" +
                "                WHERE T.ID = C.CUSTOMER_EXPAND_ID) CAR_LICENSE, \n" +
                "               b.payment_method_type,\n" +
                "               (SELECT t.NAME\n" +
                "                  FROM mas_payment_method t\n" +
                "                 WHERE T.ID = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_TYPE_NAME,\n" +
                "               C.COMPANY_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = C.COMPANY_ID) COMPANY_NAME,\n" +
                "               0 AMOUNT,\n" +
                "               0 DS_AMT,\n" +
                "               B.AMOUNT YS_AMT,\n" +
                "               0 ZJSR_AMT\n" +
                "          FROM ADVANCE_PAYMENT_METHOD B, ADVANCE_PAYMENT C\n" +
                "         WHERE B.payment_id = C.ID\n" +
                "           AND C.STATE = 'approve'\n" +
                "           AND B.PAYMENT_METHOD_TYPE IN ('09')\n");
        stringBuffer.append("        UNION ALL\n" +
                "        SELECT A.Create_Date DATE_APPROVE,\n" +
                "               A.DEPT_ID,\n" +
                "               (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID) DEPT_NAME,\n" +
                "                A.ID RETAIL_ID,\n" +
                "               '' MOBILE,\n" +
                "               '' CAR_LICENSE, \n" +
                "               '' PAYMENT_METHOD_TYPE,\n" +
                "               '' PAYMENT_METHOD_TYPE_NAME,\n" +
                "               A.COMPANY_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = A.COMPANY_ID) COMPANY_NAME,\n" +
                "               0 AMOUNT,\n" +
                "               0 DS_AMT,\n" +
                "               B.PRICE_TOTAL YS_AMT,\n" +
                "               0 ZJSR_AMT\n" +
                "          FROM ACC_VOUCHER a, ACC_VOUCHER_ITEM B\n" +
                "         WHERE A.ID = B.VOUCHER_ID\n" +
                "           AND A.VOUCHER_TYPE = 'CUSTOMER_SALE'" +
                "           AND a.ORIGIN_BUSINESS_TYPE<>'ProfitAndLoss' \n" +
                "           AND ((SELECT t.Party_Id\n" +
                "                   FROM MAS_PARTY_ROLE t\n" +
                "                  WHERE T.ID = A.COMPANY_ID) !=\n" +
                "               (SELECT t.Party_Id\n" +
                "                   FROM MAS_PARTY_ROLE t\n" +
                "                  WHERE T.ID = A.PARTNER_ID))\n" );
        stringBuffer.append("        UNION ALL\n" +
                "        SELECT A.Create_Date DATE_APPROVE,\n" +
                "               A.DEPT_ID,\n" +
                "               (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID) DEPT_NAME,\n" +
                "                A.ID RETAIL_ID,\n" +
                "               '' MOBILE,\n" +
                "               A.CAR_LICENSE, \n" +
                "               '' PAYMENT_METHOD_TYPE,\n" +
                "               '' PAYMENT_METHOD_TYPE_NAME,\n" +
                "               A.PAYMENT_ID COMPANY_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = A.PAYMENT_ID) COMPANY_NAME,\n" +
                "               0 AMOUNT,\n" +
                "               0 DS_AMT,\n" +
                "               A.AMOUNT YS_AMT,\n" +
                "               0 ZJSR_AMT\n" +
                "          FROM FN_SETTLE_EXPENSE A\n" +
                "         WHERE A.EXPENSE_TYPE = 'infoExpense'\n" +
                "           AND A.TYPE = 'in'\n");
        stringBuffer.append("        UNION ALL\n" +
                "        SELECT C.post_date DATE_APPROVE,\n" +
                "               C.DEPT_ID,\n" +
                "               (SELECT NAME FROM MAS_DEPT WHERE ID = C.DEPT_ID) DEPT_NAME,\n" +
                "                c.IMCOME_ID RETAIL_ID,\n" +
                "               '' MOBILE,\n" +
                "               '' CAR_LICENSE, \n" +
                "               E.PAYMENT_METHOD_TYPE,\n" +
                "               M.name PAYMENT_METHOD_TYPE_NAME,\n" +
                "               C.COMPANY_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = C.COMPANY_ID) COMPANY_NAME,\n" +
                "               0 AMOUNT,\n" +
                "               0 DS_AMT,\n" +
                "               0 YS_AMT,\n" +
                "               E.INCOME_AMOUNT ZJSR_AMT\n" +
                "          from (SELECT A.ID         DEPT_ID,\n" +
                "                       B.ID         IMCOME_ID,\n" +
                "                       B.POST_DATE,\n" +
                "                       B.COMPANY_ID\n" +
                "                  FROM MAS_DEPT A, MONEY_INCOME B\n" +
                "                 WHERE  A.id = B.DEPT_id\n" +
                "                   AND A.PROFIT_CENTER_ID = B.PROFIT_CENTER_ID\n" +
                "                   AND B.STATE = 'approve') C,MONEY_INCOME_ITEM E,Mas_Payment_Method M\n" +
                "         WHERE C.IMCOME_ID = E.MONEY_INCOME AND E.PAYMENT_METHOD_TYPE = M.ID \n" );
        stringBuffer.append("        UNION ALL\n" +
                "        SELECT c.date_approve,\n" +
                "               C.DEPT_ID,\n" +
                "               (SELECT NAME FROM MAS_DEPT WHERE ID = C.DEPT_ID) DEPT_NAME,\n" +
                "                C.ID RETAIL_ID,\n" +
                "               (SELECT (GET_ENCRYPT_MOBILE(T.MOBILE)) AS MOBILE\n" +
                "                       FROM FN_CUSTOMER_EXPAND T\n" +
                "                          WHERE T.ID = C.CUSTOMER_EXPAND_ID) MOBILE,\n" +
                "               (SELECT T.CAR_LICENSE\n" +
                "                 FROM FN_CUSTOMER_EXPAND T\n" +
                "                WHERE T.ID = C.CUSTOMER_EXPAND_ID) CAR_LICENSE,\n" +
                "               b.payment_method_type,\n" +
                "               (SELECT t.NAME\n" +
                "                  FROM mas_payment_method t\n" +
                "                 WHERE T.ID = B.PAYMENT_METHOD_TYPE) PAYMENT_METHOD_TYPE_NAME,\n" +
                "               C.COMPANY_ID,\n" +
                "               (SELECT C.NAME\n" +
                "                  FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "                 WHERE C.ID = D.PARTY_ID\n" +
                "                   AND D.ID = C.COMPANY_ID) COMPANY_NAME,\n" +
                "               0 AMOUNT,\n" +
                "               B.AMOUNT DS_AMT,\n" +
                "               0 YS_AMT,\n" +
                "               0 ZJSR_AMT\n" +
                "          FROM RETAIL_ORDER_PAYMENT_METHOD B, RETAIL_ORDER C\n" +
                "         WHERE B.RETAIL_ORDER_ID = C.ID\n" +
//                "           AND C.STATE IN ('done', 'confirmed')\n" +
                "           AND B.PAYMENT_METHOD_TYPE NOT IN ('10', '12', '05', '09')\n" +
                "           AND C.ORDER_TYPE IN\n" +
                "               ('agencyPay', 'noDirectPay', 'directPay', 'DS0'))\n" +
                " WHERE 1 = 1");

        JSONArray DATE_OF_STATISTICS = param.getJSONArray("DATE_OF_STATISTICS");
        if (DATE_OF_STATISTICS!=null) {
            String DATE_OF_STATISTICS_S = DATE_OF_STATISTICS.getString(0);
            Date DATE_OF_STATISTICS_STATE = StringToDate(DATE_OF_STATISTICS_S,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_STATE_S = DateUtil.format(DATE_OF_STATISTICS_STATE,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_STATE",DATE_OF_STATISTICS_STATE_S);
            String DATE_OF_STATISTICS_E = DATE_OF_STATISTICS.getString(1);
            Date DATE_OF_STATISTICS_END = StringToDate(DATE_OF_STATISTICS_E,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_END_S = DateUtil.format(DATE_OF_STATISTICS_END,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_END",DATE_OF_STATISTICS_END_S);
        }
        if(param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_STATE") != null)  stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd')  ");
        if(param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_END") != null)  stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
        if(param.get("COMPANY_ID") != null ) stringBuffer.append(" AND COMPANY_ID = :COMPANY_ID ");
        if(param.get("DEPT_ID") != null )  stringBuffer.append(" AND DEPT_ID = :DEPT_ID ");
        if(param.get("DATE_APPROVE") != null)  stringBuffer.append(" AND DATE_APPROVE >= to_date(:DATE_APPROVE,'yyyy-MM-dd') ");
        if(param.get("DATE_APPROVE") != null)  stringBuffer.append(" AND DATE_APPROVE < to_date(:DATE_APPROVE,'yyyy-MM-dd') +1");
        stringBuffer.append("  GROUP BY date_approve, DEPT_NAME,RETAIL_ID ,MOBILE,CAR_LICENSE, PAYMENT_METHOD_TYPE_NAME, COMPANY_NAME");
        return toJSONArray(find(stringBuffer.toString(), param));
    }

    /**
     * String --> Date
     * @param endDate
     * @param dateFormat
     * @return
     */
    public Date StringToDate( String endDate, String dateFormat){
        DateFormat format = new SimpleDateFormat(dateFormat);
        Date eDate = null;
        try {
            eDate = format.parse(endDate);
        }catch  (ParseException e) {
            e.printStackTrace();
        }
        return eDate;
    }
    /**
     * 车险佣金
     * @param param
     * @return
     */
    public JSONArray find_receivableInMxBycommission(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append(" SELECT B.FN_COMMISSION AS AMOUNT,\n" +
                "        A.DEPT_ID,\n" +
                "        (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID) DEPT_NAME,\n" +
                "        A.COMPANY_ID,\n" +
                "        (SELECT C.NAME\n" +
                "           FROM MAS_PARTY C, MAS_PARTY_ROLE D\n" +
                "          WHERE C.ID = D.PARTY_ID\n" +
                "            AND D.ID = A.COMPANY_ID) COMPANY_NAME,\n" +
                "        \n" +
                "        (CASE\n" +
                "          WHEN A.INSURANCE_TYPE = '0' THEN\n" +
                "           '商业险'\n" +
                "          WHEN A.INSURANCE_TYPE = '1' THEN\n" +
                "           '交强险'\n" +
                "          ELSE\n" +
                "           A.INSURANCE_TYPE\n" +
                "        END) INSURANCE_TYPE, A.PLATE_LICENSE, A.VIN, \n" +
                "        A.INSURANCE_AMOUNT, \n" +
                "        TO_CHAR(A.CREATE_DATE, 'yyyy-MM-dd') DATE_APPROVE,       \n" +
                "        'form/purch.voucher' FORM_ID,\n" +
                "        'voucher.findOne' API,\n" +
                "        '车险佣金' ACCOUNTTYPE,\n" +
                "        C.NAME AS COMMISSION_TYPE\n" +
                "   FROM POLICY_INFORMATION A, POLICY_INFORMATION_ITEM B,Soe_Business_Attr_Value C\n" +
                "  WHERE A.ID = B.POLICY_INFORMATION_ID\n" +
                "  AND C.ID =  B.COMMISSION_TYPE");
        JSONArray DATE_OF_STATISTICS = param.getJSONArray("DATE_OF_STATISTICS");
        if (DATE_OF_STATISTICS!=null) {
            String DATE_OF_STATISTICS_S = DATE_OF_STATISTICS.getString(0);
            Date DATE_OF_STATISTICS_STATE = StringToDate(DATE_OF_STATISTICS_S,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_STATE_S = DateUtil.format(DATE_OF_STATISTICS_STATE,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_STATE",DATE_OF_STATISTICS_STATE_S);
            String DATE_OF_STATISTICS_E = DATE_OF_STATISTICS.getString(1);
            Date DATE_OF_STATISTICS_END = StringToDate(DATE_OF_STATISTICS_E,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_END_S = DateUtil.format(DATE_OF_STATISTICS_END,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_END",DATE_OF_STATISTICS_END_S);
        }

        if(param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_STATE") != null)  stringBuffer.append(" AND A.CREATE_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd')  ");
        if(param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_END") != null)  stringBuffer.append(" AND A.CREATE_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
        if(param.get("COMPANY_ID") != null ) stringBuffer.append(" AND COMPANY_ID = :COMPANY_ID ");
        if(param.get("DEPT_ID") != null )  stringBuffer.append(" AND DEPT_ID = :DEPT_ID ");
        if(param.get("DATE_APPROVE") != null)  stringBuffer.append(" AND A.CREATE_DATE >= to_date(:DATE_APPROVE,'yyyy-MM-dd') ");
        if(param.get("DATE_APPROVE") != null)  stringBuffer.append(" AND A.CREATE_DATE < to_date(:DATE_APPROVE,'yyyy-MM-dd') +1");
        return toJSONArray(find(stringBuffer.toString(), param));

    }

    public JSONArray find_receivableInMxByUsedCar(JSONObject param) {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append(" SELECT A.STORE_PRICE AS AMOUNT,\n" +
                "        A.DEPT_ID,\n" +
                "        (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID) DEPT_NAME,\n" +
                "        A.LICENSE_PLATE,\n" +
                "        A.FINAL_PRICE,\n" +
                "        TO_CHAR(A.CREATE_DATE, 'yyyy-MM-dd') DATE_OF_STATISTICS,       \n" +
                "        'form/purch.voucher' FORM_ID,\n" +
                "        'voucher.findOne' API\n" +
                "   FROM WAR_USED_CAR A , MAS_DEPT B\n" +
                "  WHERE A.DEPT_ID = B.ID\n" +
                "   AND A.STATE = 'approve'");
        JSONArray DATE_OF_STATISTICS = param.getJSONArray("DATE_OF_STATISTICS");
        if (DATE_OF_STATISTICS!=null) {
            String DATE_OF_STATISTICS_S = DATE_OF_STATISTICS.getString(0);
            Date DATE_OF_STATISTICS_STATE = StringToDate(DATE_OF_STATISTICS_S,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_STATE_S = DateUtil.format(DATE_OF_STATISTICS_STATE,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_STATE",DATE_OF_STATISTICS_STATE_S);
            String DATE_OF_STATISTICS_E = DATE_OF_STATISTICS.getString(1);
            Date DATE_OF_STATISTICS_END = StringToDate(DATE_OF_STATISTICS_E,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_END_S = DateUtil.format(DATE_OF_STATISTICS_END,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_END",DATE_OF_STATISTICS_END_S);
        }

        if(param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_STATE") != null)  stringBuffer.append(" AND A.CREATE_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd')  ");
        if(param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_END") != null)  stringBuffer.append(" AND A.CREATE_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
        if(param.get("COMPANY_ID") != null ) stringBuffer.append(" AND B.COMPANY_ID = :COMPANY_ID ");
        if(param.get("DEPT_ID") != null )  stringBuffer.append(" AND DEPT_ID = :DEPT_ID ");
        if(param.get("DATE_APPROVE") != null)  stringBuffer.append(" AND A.DATE_APPROVE >= to_date(:DATE_APPROVE,'yyyy-MM-dd') ");
        if(param.get("DATE_APPROVE") != null)  stringBuffer.append(" AND A.DATE_APPROVE < to_date(:DATE_APPROVE,'yyyy-MM-dd') +1");

        return toJSONArray(find(stringBuffer.toString(), param));
    }

    /**
     * 会员跨公司消费-收入
     * @param param
     * @return
     */
    public JSONArray findCrossCompanyIncome(JSONObject param) throws ParseException {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("SELECT A.COMPANY_ID                                                              GSID,\n" +
                "       '[' || A.COMPANY_ID || ']' || (SELECT NAME\n" +
                "                                      FROM MAS_PARTY A,\n" +
                "                                           MAS_PARTY_ROLE P\n" +
                "                                      WHERE A.ID = P.PARTY_ID\n" +
                "                                        AND P.ID = A.COMPANY_ID)                 COMPANY,\n" +
                "       (SELECT NAME\n" +
                "        FROM MAS_PARTY A,\n" +
                "             MAS_PARTY_ROLE P\n" +
                "        WHERE A.ID = P.PARTY_ID\n" +
                "          AND P.ID = A.COMPANY_ID)                                               GSMC,\n" +
                "       A.PARTNER_COMPANY_ID\n" +
                "                                                                                 SSGSID,\n" +
                "       '[' || A.PARTNER_COMPANY_ID || ']' || (SELECT NAME\n" +
                "                                              FROM MAS_PARTY A,\n" +
                "                                                   MAS_PARTY_ROLE P\n" +
                "                                              WHERE A.ID = P.PARTY_ID\n" +
                "                                                AND P.ID = A.PARTNER_COMPANY_ID) PARTNER_COMPANY,\n" +
                "       (SELECT NAME\n" +
                "        FROM MAS_PARTY A,\n" +
                "             MAS_PARTY_ROLE P\n" +
                "        WHERE A.ID = P.PARTY_ID\n" +
                "          AND P.ID = A.PARTNER_COMPANY_ID)                                       SSGSMC,\n" +
                "       TO_CHAR(A.STATISTIC_DATE,\n" +
                "               'yyyy-MM-dd')                                                     TJRQ,\n" +
                "       A.USE_SAVINGS,\n" +
                "       A.GIVE_SAVINGS,\n" +
                "       A.AMOUNT_USED_HB,\n" +
                "       A.AMOUNT_USED_BB\n" +
                "        ,\n" +
                "       '收入'                                                                      TYPE,\n" +
                "       A.SETTLEMENT_AMOUNT_HB,\n" +
                "       A.SETTLEMENT_AMOUNT_BB,\n" +
                "       A.amount_used_kq\n" +
                "        ,\n" +
                "       A.PARTNER_DEPT_ID                                                         SSBMID,\n" +
                "       (SELECT '[' || A.PARTNER_DEPT_ID || ']' || B.NAME\n" +
                "        FROM MAS_DEPT B\n" +
                "        WHERE B.ID = A.PARTNER_DEPT_ID)                                          PARTNER_DEPT,\n" +
                "       A.DEPT_ID                                                                 BMID,\n" +
                "       (SELECT B.NAME\n" +
                "        FROM MAS_DEPT B\n" +
                "        WHERE B.ID = A.DEPT_ID)                                                  BMMC,\n" +
                "       '[' || A.DEPT_ID || ']' || (SELECT B.NAME\n" +
                "                                   FROM MAS_DEPT B\n" +
                "                                   WHERE B.ID\n" +
                "                                             = A.DEPT_ID)                        DEPT,\n" +
                "       settlement_amount_kq,\n" +
                "       A.SETTLEMENT_AMOUNT,\n" +
                "       A.platform_management_fee,\n" +
                "      (select '[' || d.ID || ']' || d.NAME from yyesb.mas_access_provider d left outer join yyesb.s_access_company b\n" +
                "             on d.id = b.access_id left outer join yyesb.mas_party_role C  on b.company_id = c.id where c.id = a.PARTNER_COMPANY_ID) PARTNER_ACCESS_PROVIDER,\n" +
                "      (select '[' || d.ID || ']' || d.NAME from yyesb.mas_access_provider d left outer join yyesb.s_access_company b" +
                "           on d.id = b.access_id left outer join yyesb.mas_party_role C  on b.company_id = c.id where c.id = a.company_id) ACCESS_PROVIDER\n" +
                " FROM VIP_UNION_REPORT A\n" +
                " WHERE 1 = 1 " +
                " and TYPE = 'in'");

        JSONArray DATE_OF_STATISTICS = param.getJSONArray("DATE_OF_STATISTICS");
        if (DATE_OF_STATISTICS!=null) {
            String DATE_OF_STATISTICS_S = DATE_OF_STATISTICS.getString(0);
            Date DATE_OF_STATISTICS_STATE = StringToDate(DATE_OF_STATISTICS_S,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_STATE_S = DateUtil.format(DATE_OF_STATISTICS_STATE,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_STATE",DATE_OF_STATISTICS_STATE_S);
            String DATE_OF_STATISTICS_E = DATE_OF_STATISTICS.getString(1);
            Date DATE_OF_STATISTICS_END = StringToDate(DATE_OF_STATISTICS_E,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_END_S = DateUtil.format(DATE_OF_STATISTICS_END,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_END",DATE_OF_STATISTICS_END_S);
        }
        DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        if(param.get("DATE_OF_STATISTICS_STATE") != null)  stringBuffer.append(" AND A.STATISTIC_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd')  ");
        if(param.get("DATE_OF_STATISTICS_END") != null)  stringBuffer.append("  AND A.STATISTIC_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
        if(param.get("COMPANY_ID") != null ) stringBuffer.append(" AND A.COMPANY_ID = :COMPANY_ID ");
        if(param.get("BMID") != null ) stringBuffer.append(" AND A.DEPT_ID = :BMID ");
        if(param.get("TJRQ") != null) {
            try {
                Date date = formatter.parse(param.getString("TJRQ"));
                stringBuffer.append(" AND A.STATISTIC_DATE >= to_date(:TJRQ,'yyyy-MM-dd') ");
                stringBuffer.append(" AND A.STATISTIC_DATE < to_date(:TJRQ,'yyyy-MM-dd') +1");
            } catch (Exception e) {
                Date start = formatter.parse(param.getString("DATE_OF_STATISTICS_STATE"));
                Date end = formatter.parse(param.getString("DATE_OF_STATISTICS_END"));
                LocalDate localDateStart = DateUtil.UDateToLocalDate(start);
                LocalDate localDateEnd = DateUtil.UDateToLocalDate(end);
                if (localDateStart.getMonthValue() == localDateEnd.getMonthValue()) {
                    stringBuffer.append(" AND A.STATISTIC_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd') ");
                    stringBuffer.append(" AND A.STATISTIC_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
                } else {
                    formatter = new SimpleDateFormat("yyyy-MM");
                    Date dateApprove = formatter.parse(param.getString("TJRQ"));
                    LocalDate loDateApprove = DateUtil.UDateToLocalDate(dateApprove);
                    if (localDateStart.getMonthValue() == loDateApprove.getMonthValue()){
                        //获取月末
                        LocalDate monthOfLastDate = loDateApprove.with(TemporalAdjusters.lastDayOfMonth());
                        String SmonthOfLastDate = monthOfLastDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                        param.put("SmonthOfLastDate",SmonthOfLastDate);
                        stringBuffer.append(" AND A.STATISTIC_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd') ");
                        stringBuffer.append(" AND A.STATISTIC_DATE < to_date(:SmonthOfLastDate,'yyyy-MM-dd') + 1");
                    } else {
                        //获取月头
                        LocalDate monthOfFirstDate = loDateApprove.with(TemporalAdjusters.firstDayOfMonth());
                        LocalDate monthOfLastDate = loDateApprove.with(TemporalAdjusters.lastDayOfMonth());
                        String SmonthOfFirstDate = monthOfFirstDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                        String monthOfLastDateStr = monthOfLastDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                        param.put("SmonthOfFirstDate",SmonthOfFirstDate);
                        param.put("DATE_OF_STATISTICS_END",monthOfLastDateStr);
                        stringBuffer.append(" AND A.STATISTIC_DATE >= to_date(:SmonthOfFirstDate,'yyyy-MM-dd') ");
                        stringBuffer.append(" AND A.STATISTIC_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
                    }
                }

            }
        }
        stringBuffer.append(" ORDER BY A.STATISTIC_DATE");

        return toJSONArray(find(stringBuffer.toString(), param));
    }
    /**
     * 会员跨公司消费-支出
     * @param param
     * @return
     */
    public JSONArray findCrossCompanyPay(JSONObject param) throws ParseException {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("SELECT A.COMPANY_ID                                                              GSID,\n" +
                "       '[' || A.COMPANY_ID || ']' || (SELECT NAME\n" +
                "                                      FROM MAS_PARTY A,\n" +
                "                                           MAS_PARTY_ROLE P\n" +
                "                                      WHERE A.ID = P.PARTY_ID\n" +
                "                                        AND P.ID = A.COMPANY_ID)                 COMPANY,\n" +
                "       (SELECT NAME\n" +
                "        FROM MAS_PARTY A,\n" +
                "             MAS_PARTY_ROLE P\n" +
                "        WHERE A.ID = P.PARTY_ID\n" +
                "          AND P.ID = A.COMPANY_ID)                                               GSMC,\n" +
                "       A.PARTNER_COMPANY_ID\n" +
                "                                                                                  SSGSID,\n" +
                "       '[' || A.PARTNER_COMPANY_ID || ']' || (SELECT NAME\n" +
                "                                              FROM MAS_PARTY A,\n" +
                "                                                   MAS_PARTY_ROLE P\n" +
                "                                              WHERE A.ID = P.PARTY_ID\n" +
                "                                                AND P.ID = A.PARTNER_COMPANY_ID) PARTNER_COMPANY,\n" +
                "       (SELECT NAME\n" +
                "        FROM MAS_PARTY A,\n" +
                "             MAS_PARTY_ROLE P\n" +
                "        WHERE A.ID = P.PARTY_ID\n" +
                "          AND P.ID = A.PARTNER_COMPANY_ID)                                       SSGSMC,\n" +
                "       TO_CHAR(A.STATISTIC_DATE,\n" +
                "               'yyyy-MM-dd')                                                     TJRQ,\n" +
                "       A.USE_SAVINGS,\n" +
                "       A.GIVE_SAVINGS,\n" +
                "       A.AMOUNT_USED_HB,\n" +
                "       A.AMOUNT_USED_BB\n" +
                "        ,\n" +
                "       '支出'                                                                      TYPE,\n" +
                "       A.SETTLEMENT_AMOUNT_HB,\n" +
                "       A.SETTLEMENT_AMOUNT_BB,\n" +
                "       A.amount_used_kq\n" +
                "        ,\n" +
                "       A.PARTNER_DEPT_ID                                                         SSBMID,\n" +
                "       (SELECT '[' || A.PARTNER_DEPT_ID || ']' || B.NAME\n" +
                "        FROM MAS_DEPT B\n" +
                "        WHERE B.ID = A.PARTNER_DEPT_ID)                                          PARTNER_DEPT,\n" +
                "       A.DEPT_ID                                                                 BMID,\n" +
                "       (SELECT B.NAME\n" +
                "        FROM MAS_DEPT B\n" +
                "        WHERE B.ID = A.DEPT_ID)                                                  BMMC,\n" +
                "       '[' || A.DEPT_ID || ']' || (SELECT B.NAME\n" +
                "                                   FROM MAS_DEPT B\n" +
                "                                   WHERE B.ID\n" +
                "                                             = A.DEPT_ID)                        DEPT," +
                "       A.SETTLEMENT_AMOUNT * -1 AS SETTLEMENT_AMOUNT,\n" +
                "       settlement_amount_kq,\n" +
                "       A.platform_management_fee,\n" +
                "      (select '[' || d.ID || ']' || d.NAME from yyesb.mas_access_provider d left outer join yyesb.s_access_company b\n" +
                "             on d.id = b.access_id left outer join yyesb.mas_party_role C  on b.company_id = c.id where c.id = a.PARTNER_COMPANY_ID) PARTNER_ACCESS_PROVIDER,\n" +
                "       (select '[' || C.ID || ']' || C.NAME from mas_access_provider C,MAS_PARTY_ROLE D where D.ID = a.COMPANY_ID AND d.ACCESS_PROVIDER_ID = C.ID)ACCESS_PROVIDER                                                       \n" +
                " FROM VIP_UNION_REPORT A\n" +
                " WHERE 1 = 1 " +
                " and TYPE = 'out'");

        JSONArray DATE_OF_STATISTICS = param.getJSONArray("DATE_OF_STATISTICS");
        if (DATE_OF_STATISTICS!=null) {
            String DATE_OF_STATISTICS_S = DATE_OF_STATISTICS.getString(0);
            Date DATE_OF_STATISTICS_STATE = StringToDate(DATE_OF_STATISTICS_S,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_STATE_S = DateUtil.format(DATE_OF_STATISTICS_STATE,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_STATE",DATE_OF_STATISTICS_STATE_S);
            String DATE_OF_STATISTICS_E = DATE_OF_STATISTICS.getString(1);
            Date DATE_OF_STATISTICS_END = StringToDate(DATE_OF_STATISTICS_E,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_END_S = DateUtil.format(DATE_OF_STATISTICS_END,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_END",DATE_OF_STATISTICS_END_S);
        }
        DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        if(param.get("DATE_OF_STATISTICS_STATE") != null)  stringBuffer.append(" AND A.STATISTIC_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd')  ");
        if(param.get("DATE_OF_STATISTICS_END") != null)  stringBuffer.append("  AND A.STATISTIC_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
        if(param.get("COMPANY_ID") != null ) stringBuffer.append(" AND A.COMPANY_ID = :COMPANY_ID ");
        if(param.get("SSBMID") != null ) stringBuffer.append(" AND A.DEPT_ID = :SSBMID ");
        if(param.get("TJRQ") != null) {
            try {
                Date date = formatter.parse(param.getString("TJRQ"));
                stringBuffer.append(" AND A.STATISTIC_DATE >= to_date(:TJRQ,'yyyy-MM-dd') ");
                stringBuffer.append(" AND A.STATISTIC_DATE < to_date(:TJRQ,'yyyy-MM-dd') +1");
            } catch (Exception e) {
                Date start = formatter.parse(param.getString("DATE_OF_STATISTICS_STATE"));
                Date end = formatter.parse(param.getString("DATE_OF_STATISTICS_END"));
                LocalDate localDateStart = DateUtil.UDateToLocalDate(start);
                LocalDate localDateEnd = DateUtil.UDateToLocalDate(end);
                if (localDateStart.getMonthValue() == localDateEnd.getMonthValue()) {
                    stringBuffer.append(" AND A.STATISTIC_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd') ");
                    stringBuffer.append(" AND A.STATISTIC_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
                } else {
                    formatter = new SimpleDateFormat("yyyy-MM");
                    Date dateApprove = formatter.parse(param.getString("TJRQ"));
                    LocalDate loDateApprove = DateUtil.UDateToLocalDate(dateApprove);
                    if (localDateStart.getMonthValue() == loDateApprove.getMonthValue()){
                        //获取月末
                        LocalDate monthOfLastDate = loDateApprove.with(TemporalAdjusters.lastDayOfMonth());
                        String SmonthOfLastDate = monthOfLastDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                        param.put("SmonthOfLastDate",SmonthOfLastDate);
                        stringBuffer.append(" AND A.STATISTIC_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd') ");
                        stringBuffer.append(" AND A.STATISTIC_DATE < to_date(:SmonthOfLastDate,'yyyy-MM-dd') + 1");
                    } else {
                        //获取月头
                        LocalDate monthOfFirstDate = loDateApprove.with(TemporalAdjusters.firstDayOfMonth());
                        LocalDate monthOfLastDate = loDateApprove.with(TemporalAdjusters.lastDayOfMonth());
                        String SmonthOfFirstDate = monthOfFirstDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                        String monthOfLastDateStr = monthOfLastDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                        param.put("SmonthOfFirstDate",SmonthOfFirstDate);
                        param.put("DATE_OF_STATISTICS_END",monthOfLastDateStr);
                        stringBuffer.append(" AND A.STATISTIC_DATE >= to_date(:SmonthOfFirstDate,'yyyy-MM-dd') ");
                        stringBuffer.append(" AND A.STATISTIC_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
                    }
                }

            }
        }
        stringBuffer.append(" ORDER BY A.STATISTIC_DATE");

        return toJSONArray(find(stringBuffer.toString(), param));
    }

    /**
     * 平台引流明细-平台券补贴
     * @param param
     * @return
     */
    public JSONArray findPlatformSubsidy(JSONObject param) throws ParseException {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("" +
                "       select A.COMPANY_ID,\n" +
                "           to_char(trunc(A.STATISTIC_DATE),'YYYY-MM-DD')STATISTIC_DATE,\n" +
                "           A.PLATFORM_ID,\n" +
                "           A.ORDER_ID,\n" +
                "           A.DEPT_ID,\n" +
                "           A.CAR_LICENSE,\n" +
                "           A.PRODUCT_ID,\n" +
                "           B.NAME                    PRODUCT_NAME,\n" +
                "           A.SALES_AMOUNT,\n" +
                "           DECODE(A.FLOW_TYPE,\n" +
                "                  'offlineCompensationFlow', '线下支付引流费',\n" +
                "                  'onlinePayFlow', '线上支付引流费用',\n" +
                "                  'platformSubsidy', '平台券补贴',\n" +
                "                  A.FLOW_TYPE)       FLOW_TYPE,\n" +
                "           A.DISCOUNT_AMOUNT,\n" +
                "           A.PLATFORM_COUPON_SUBSIDY,\n" +
                "           to_char(trunc(A.ORDER_CREATOR_DATE),'YYYY-MM-DD')ORDER_CREATOR_DATE,\n" +
                "           (SELECT C.NAME\n" +
                "            FROM MAS_PARTY_ROLE B,\n" +
                "                 MAS_PARTY C\n" +
                "            WHERE A.COMPANY_ID = B.ID\n" +
                "              AND B.PARTY_ID = C.ID) COMPANY_NAME,\n" +
                "           (SELECT C.NAME\n" +
                "            FROM MAS_PARTY_ROLE B,\n" +
                "                 MAS_PARTY C\n" +
                "            WHERE A.PLATFORM_ID = B.ID\n" +
                "              AND B.PARTY_ID = C.ID) PLATFORM_NAME," +
                "               (SELECT C.NAME FROM MAS_DEPT C WHERE C.ID=A.DEPT_ID)DEPT_NAME,\n" +
                "           A.PLATFORM_COUPON_SUBSIDY PLATFORM_SUBSIDY\n" +
                "    FROM PLATFORM_FLOW A\n" +
                "             left join MAS_PRODUCT B on b.id = a.PRODUCT_ID\n" +
                "    WHERE FLOW_TYPE = 'platformSubsidy'\n");

        JSONArray DATE_OF_STATISTICS = param.getJSONArray("DATE_OF_STATISTICS");
        if (DATE_OF_STATISTICS!=null) {
            String DATE_OF_STATISTICS_S = DATE_OF_STATISTICS.getString(0);
            Date DATE_OF_STATISTICS_STATE = StringToDate(DATE_OF_STATISTICS_S,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_STATE_S = DateUtil.format(DATE_OF_STATISTICS_STATE,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_STATE",DATE_OF_STATISTICS_STATE_S);
            String DATE_OF_STATISTICS_E = DATE_OF_STATISTICS.getString(1);
            Date DATE_OF_STATISTICS_END = StringToDate(DATE_OF_STATISTICS_E,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_END_S = DateUtil.format(DATE_OF_STATISTICS_END,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_END",DATE_OF_STATISTICS_END_S);
        }
        DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        if(param.get("DATE_OF_STATISTICS_STATE") != null)  stringBuffer.append(" AND A.STATISTIC_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd')  ");
        if(param.get("DATE_OF_STATISTICS_END") != null)  stringBuffer.append("  AND A.STATISTIC_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
        if(param.get("COMPANY_ID") != null ) stringBuffer.append(" AND A.COMPANY_ID = :COMPANY_ID ");
        if(param.get("STATISTIC_DATE") != null) {
            try {
                Date date = formatter.parse(param.getString("STATISTIC_DATE"));
                stringBuffer.append(" AND A.STATISTIC_DATE >= to_date(:STATISTIC_DATE,'yyyy-MM-dd') ");
                stringBuffer.append(" AND A.STATISTIC_DATE < to_date(:STATISTIC_DATE,'yyyy-MM-dd') +1");
            } catch (Exception e) {
                Date start = formatter.parse(param.getString("DATE_OF_STATISTICS_STATE"));
                Date end = formatter.parse(param.getString("DATE_OF_STATISTICS_END"));
                LocalDate localDateStart = DateUtil.UDateToLocalDate(start);
                LocalDate localDateEnd = DateUtil.UDateToLocalDate(end);
                if (localDateStart.getMonthValue() == localDateEnd.getMonthValue()) {
                    stringBuffer.append(" AND A.STATISTIC_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd') ");
                    stringBuffer.append(" AND A.STATISTIC_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
                } else {
                    formatter = new SimpleDateFormat("yyyy-MM");
                    Date dateApprove = formatter.parse(param.getString("STATISTIC_DATE"));
                    LocalDate loDateApprove = DateUtil.UDateToLocalDate(dateApprove);
                    if (localDateStart.getMonthValue() == loDateApprove.getMonthValue()){
                        //获取月末
                        LocalDate monthOfLastDate = loDateApprove.with(TemporalAdjusters.lastDayOfMonth());
                        String SmonthOfLastDate = monthOfLastDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                        param.put("SmonthOfLastDate",SmonthOfLastDate);
                        stringBuffer.append(" AND A.STATISTIC_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd') ");
                        stringBuffer.append(" AND A.STATISTIC_DATE < to_date(:SmonthOfLastDate,'yyyy-MM-dd') + 1");
                    } else {
                        //获取月头
                        LocalDate monthOfFirstDate = loDateApprove.with(TemporalAdjusters.firstDayOfMonth());
                        String SmonthOfFirstDate = monthOfFirstDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                        param.put("SmonthOfFirstDate",SmonthOfFirstDate);
                        stringBuffer.append(" AND A.STATISTIC_DATE >= to_date(:SmonthOfFirstDate,'yyyy-MM-dd') ");
                        stringBuffer.append(" AND A.STATISTIC_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
                    }
                }

            }
        }
        stringBuffer.append(" ORDER BY A.STATISTIC_DATE");

        return toJSONArray(find(stringBuffer.toString(), param));
    }

    /**
     * 平台引流明细-线上线下引流费用
     * @param param
     * @return
     */
    public JSONArray findPlatformFlowPay(JSONObject param) throws ParseException {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("" +
                "       select A.COMPANY_ID,\n" +
                "              to_char(trunc(A.STATISTIC_DATE), 'YYYY-MM-DD')         STATISTIC_DATE,\n" +
                "              A.PLATFORM_ID,\n" +
                "              A.ORDER_ID,\n" +
                "              A.DEPT_ID,\n" +
                "              A.CAR_LICENSE,\n" +
                "              A.PRODUCT_ID,\n" +
                "              B.NAME                                                 PRODUCT_NAME,\n" +
                "              A.SALES_AMOUNT,\n" +
                "              DECODE(A.FLOW_TYPE,\n" +
                "                     'offlineCompensationFlow', '线下支付引流费', 'onlinePayFlow', '线上支付引流费用', 'platformSubsidy', '平台券补贴',\n" +
                "                     A.FLOW_TYPE)                                    FLOW_TYPE,\n" +
                "              A.DISCOUNT_AMOUNT,\n" +
                "              A.PLATFORM_COUPON_SUBSIDY,\n" +
                "              to_char(trunc(A.ORDER_CREATOR_DATE), 'YYYY-MM-DD')     ORDER_CREATOR_DATE,\n" +
                "              (SELECT C.NAME\n" +
                "               FROM MAS_PARTY_ROLE B,\n" +
                "                    MAS_PARTY C\n" +
                "               WHERE A.COMPANY_ID = B.ID\n" +
                "                 AND B.PARTY_ID\n" +
                "                   = C.ID)                                           COMPANY_NAME,\n" +
                "              (select C.NAME FROM MAS_DEPT C WHERE C.ID = A.DEPT_ID) DEPT_NAME,\n" +
                "              (SELECT C.NAME\n" +
                "               FROM MAS_PARTY_ROLE B,\n" +
                "                    MAS_PARTY C\n" +
                "               WHERE A.PLATFORM_ID\n" +
                "                   = B.ID\n" +
                "                 AND B.PARTY_ID = C.ID)                              PLATFORM_NAME,\n" +
                "       \n" +
                "              A.OFFLINE_COMPENSATION_FLOW                            OFFLINE_COMPENSATION_FLOW,\n" +
                "              A.PAY_AMOUNT                                           PAY_AMOUNT,\n" +
                "              D.ID                                                   CLASSIFY_ID,\n" +
                "              D.NAME                                                 CLASSIFY_NAME,\n" +
                "              A.ONLINE_PAY_FLOW                                      ONLINE_PAY_FLOW\n" +
                "       FROM PLATFORM_FLOW A\n" +
                "                left join MAS_PRODUCT B on b.id = a.PRODUCT_ID\n" +
                "                left join MAS_CLASSIFY D on D.id = B.CLASSIFY_ID\n" +
                "    WHERE FLOW_TYPE  IN ('onlinePayFlow','offlineCompensationFlow')\n");

        JSONArray DATE_OF_STATISTICS = param.getJSONArray("DATE_OF_STATISTICS");
        if (DATE_OF_STATISTICS!=null) {
            String DATE_OF_STATISTICS_S = DATE_OF_STATISTICS.getString(0);
            Date DATE_OF_STATISTICS_STATE = StringToDate(DATE_OF_STATISTICS_S,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_STATE_S = DateUtil.format(DATE_OF_STATISTICS_STATE,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_STATE",DATE_OF_STATISTICS_STATE_S);
            String DATE_OF_STATISTICS_E = DATE_OF_STATISTICS.getString(1);
            Date DATE_OF_STATISTICS_END = StringToDate(DATE_OF_STATISTICS_E,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_END_S = DateUtil.format(DATE_OF_STATISTICS_END,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_END",DATE_OF_STATISTICS_END_S);
        }
        DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        if(param.get("DATE_OF_STATISTICS_STATE") != null)  stringBuffer.append(" AND A.STATISTIC_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd')  ");
        if(param.get("DATE_OF_STATISTICS_END") != null)  stringBuffer.append("  AND A.STATISTIC_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
        if(param.get("EXPENDITURE_TYPE_NAME") != null)  stringBuffer.append("  AND A.FLOW_TYPE = '"+("线上支付引流费".equals(param.get("EXPENDITURE_TYPE_NAME"))?"onlinePayFlow":"offlineCompensationFlow")+"'  ");//value -> 线上支付引流费
        if(param.get("COMPANY_ID") != null ) stringBuffer.append(" AND A.COMPANY_ID = :COMPANY_ID ");
        if(param.get("STATISTIC_DATE") != null) {
            try {
                Date date = formatter.parse(param.getString("STATISTIC_DATE"));
                stringBuffer.append(" AND A.STATISTIC_DATE >= to_date(:STATISTIC_DATE,'yyyy-MM-dd') ");
                stringBuffer.append(" AND A.STATISTIC_DATE < to_date(:STATISTIC_DATE,'yyyy-MM-dd') +1");
            } catch (Exception e) {
                Date start = formatter.parse(param.getString("DATE_OF_STATISTICS_STATE"));
                Date end = formatter.parse(param.getString("DATE_OF_STATISTICS_END"));
                LocalDate localDateStart = DateUtil.UDateToLocalDate(start);
                LocalDate localDateEnd = DateUtil.UDateToLocalDate(end);
                if (localDateStart.getMonthValue() == localDateEnd.getMonthValue()) {
                    stringBuffer.append(" AND A.STATISTIC_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd') ");
                    stringBuffer.append(" AND A.STATISTIC_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
                } else {
                    formatter = new SimpleDateFormat("yyyy-MM");
                    Date dateApprove = formatter.parse(param.getString("STATISTIC_DATE"));
                    LocalDate loDateApprove = DateUtil.UDateToLocalDate(dateApprove);
                    if (localDateStart.getMonthValue() == loDateApprove.getMonthValue()){
                        //获取月末
                        LocalDate monthOfLastDate = loDateApprove.with(TemporalAdjusters.lastDayOfMonth());
                        String SmonthOfLastDate = monthOfLastDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                        param.put("SmonthOfLastDate",SmonthOfLastDate);
                        stringBuffer.append(" AND A.STATISTIC_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd') ");
                        stringBuffer.append(" AND A.STATISTIC_DATE < to_date(:SmonthOfLastDate,'yyyy-MM-dd') + 1");
                    } else {
                        //获取月头
                        LocalDate monthOfFirstDate = loDateApprove.with(TemporalAdjusters.firstDayOfMonth());
                        String SmonthOfFirstDate = monthOfFirstDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                        param.put("SmonthOfFirstDate",SmonthOfFirstDate);
                        stringBuffer.append(" AND A.STATISTIC_DATE >= to_date(:SmonthOfFirstDate,'yyyy-MM-dd') ");
                        stringBuffer.append(" AND A.STATISTIC_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1");
                    }
                }

            }
        }
        stringBuffer.append(" ORDER BY A.STATISTIC_DATE");

        return toJSONArray(find(stringBuffer.toString(), param));
    }

    /**
     * 喷漆接收幅数
     * @param param
     * @return
     * @throws ParseException
     */
    public JSONArray find_receivePaint(JSONObject param) throws ParseException {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("SELECT SUM(CASE\n" +
                "               WHEN n.STATE = 'receivables' THEN (n.SPECIFICATIONS * n.SALE_QTY)\n" +
                "               ELSE 0 END)  QTY,\n" +
                "       (SELECT d.name FROM MAS_DEPT d WHERE d.ID = n.SALE_DEPT_ID) SALE_DEPT_NAME,SALE_DEPT_ID," +
                "       (SELECT d.name FROM MAS_DEPT d WHERE d.ID = n.DEPT_ID) DEPT_NAME," +
                "       n.DEPT_ID," +
                "  to_char(:DATE_OF_STATISTICS_STATE) DATE_OF_STATISTICS_STATE ,to_char(:DATE_OF_STATISTICS_END) DATE_OF_STATISTICS_END\n" +
                "FROM (SELECT p.STATE,\n" +
                "             (SELECT T.SPECIFICATIONS FROM MAS_PRODUCT T WHERE T.ID = I.PRODUCT_ID) SPECIFICATIONS,\n" +
                "             I.SALE_QTY,\n" +
                "             P.SALE_DEPT_ID                                       SALE_DEPT_ID,\n" +
                "             (CASE\n" +
                "                                       WHEN o.INNER_DEPT_ID IS NOT NULL then o.INNER_DEPT_ID\n" +
                "                                       ELSE p.BELONG_DEPT_ID END)                                                       DEPT_ID \n" +
                "      FROM SER_SERVICE_PLAN p,\n" +
                "           SER_SERVICE_PLAN_ITEM i,\n" +
                "           SER_MAINTENANCE_ORDER o\n" +
                "      WHERE p.id = i.SERVICE_PLAN_ID\n" +
                "        AND o.SERVICE_PLAN_ITEM = i.ID\n" +
                "        AND o.WORK_STATE = 'finish'\n" +
                "        AND (SELECT A.CLASSIFY_ID FROM MAS_PRODUCT A WHERE A.ID = i.PRODUCT_ID) LIKE 'S0106%' ");
        JSONArray DATE_OF_STATISTICS = param.getJSONArray("DATE_OF_STATISTICS");
        try {
            JSONArray DATE_APPROVE_S = param.getJSONArray("DATE_APPROVE");
            if (DATE_APPROVE_S.size() == 2) {
                DATE_OF_STATISTICS = DATE_APPROVE_S;
                param.put("DATE_APPROVE", null);
            }
        } catch (Exception e) {

        }
        if (DATE_OF_STATISTICS!=null) {
            String DATE_OF_STATISTICS_S = DATE_OF_STATISTICS.getString(0);
            Date DATE_OF_STATISTICS_STATE = StringToDate(DATE_OF_STATISTICS_S,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_STATE_S = DateUtil.format(DATE_OF_STATISTICS_STATE,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_STATE",DATE_OF_STATISTICS_STATE_S);
            String DATE_OF_STATISTICS_E = DATE_OF_STATISTICS.getString(1);
            Date DATE_OF_STATISTICS_END = StringToDate(DATE_OF_STATISTICS_E,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_END_S = DateUtil.format(DATE_OF_STATISTICS_END,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_END",DATE_OF_STATISTICS_END_S);
        }
        DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        if(param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_STATE") != null)  stringBuffer.append("   AND (\n" +
                "            O.FINISH_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd')\n" +
                "                )  ");
        if(param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_END") != null)  stringBuffer.append("   AND (\n" +
                "            O.FINISH_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1\n" +
                "                )  ");
        if(param.get("DEPT_ID") != null )  stringBuffer.append("    AND (i.PARENT_ITEM_ID IS NOT NULL AND p.BELONG_DEPT_ID = :DEPT_ID AND\n" +
                "                                              p.BELONG_DEPT_ID = p.SERVICE_DEPT_ID AND\n" +
                "                                              p.BELONG_DEPT_ID <> p.SALE_DEPT_ID)  ");
        if(param.get("DATE_APPROVE") != null) {
            try {
                Date date = formatter.parse(param.getString("DATE_APPROVE"));
                stringBuffer.append("   AND (\n" +
                        "               O.FINISH_DATE >= to_date(:DATE_APPROVE,'yyyy-MM-dd')\n" +
                        "                )  ");
                stringBuffer.append("   AND (\n" +
                        "              ( O.FINISH_DATE < to_date(:DATE_APPROVE,'yyyy-MM-dd') + 1)\n" +
                        "                )  ");

            } catch (Exception e) {
                Date start = formatter.parse(param.getString("DATE_OF_STATISTICS_STATE"));
                Date end = formatter.parse(param.getString("DATE_OF_STATISTICS_END"));
                LocalDate localDateStart = DateUtil.UDateToLocalDate(start);
                LocalDate localDateEnd = DateUtil.UDateToLocalDate(end);
                if (localDateStart.getMonthValue() == localDateEnd.getMonthValue()) {
                    stringBuffer.append("   AND (\n" +
                            "           O.FINISH_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd')\n" +
                            "                )  ");
                    stringBuffer.append("   AND (\n" +
                            "             O.FINISH_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1\n" +
                            "                )  ");
                } else {
                    formatter = new SimpleDateFormat("yyyy-MM");
                    Date dateApprove = formatter.parse(param.getString("DATE_APPROVE"));
                    LocalDate loDateApprove = DateUtil.UDateToLocalDate(dateApprove);
                    if (localDateStart.getMonthValue() == loDateApprove.getMonthValue()){
                        //获取月末
                        LocalDate monthOfLastDate = loDateApprove.with(TemporalAdjusters.lastDayOfMonth());
                        String SmonthOfLastDate = monthOfLastDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                        param.put("DATE_OF_STATISTICS_END",SmonthOfLastDate);
                        stringBuffer.append("   AND (\n" +
                                "           O.FINISH_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd')\n" +
                                "                )  ");
                        stringBuffer.append("   AND (\n" +
                                "         O.FINISH_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1\n" +
                                "                )  ");
                    } else {
                        //获取月头
                        LocalDate monthOfFirstDate = loDateApprove.with(TemporalAdjusters.firstDayOfMonth());
                        String SmonthOfFirstDate = monthOfFirstDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                        param.put("DATE_OF_STATISTICS_STATE",SmonthOfFirstDate);
                        stringBuffer.append("   AND (\n" +
                                "            O.FINISH_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd')\n" +
                                "                )  ");
                        stringBuffer.append("   AND (\n" +
                                "             O.FINISH_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1\n" +
                                "                )  ");
                    }
                }

            }
        }
        stringBuffer.append("     ) n\n" +
                "GROUP BY n.SALE_DEPT_ID,DEPT_ID ");
        return toJSONArray(find(stringBuffer.toString(), param));
    }


    /**
     * 喷漆接收幅数明细
     * @param param
     * @return
     * @throws ParseException
     */
    public JSONArray find_receivePaintMX(JSONObject param) throws ParseException {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("SELECT p.ID,\n" +
                "       p.CAR_LISCENCE,\n" +
                "       TO_CHAR(O.FINISH_DATE, 'YYYY-MM-DD HH24:MI:SS')  FINISH_DATE,\n" +
                "       f.REALNAME,\n" +
                "       f.MOBILE,\n" +
                "       I.SALE_QTY,\n" +
                "       I.PRICE_UNIT,\n" +
                "       I.PFT_PRICE_UNIT,\n" +
                "       i.REFER_TAX,\n" +
                "       DECODE(o.STATE, 'finish', '完成', '')                                                                    MAINTENANCE_ORDER_STATE,\n" +
                "       DECODE(p.STATE, 'confirm', '确认',\n" +
                "              'qualityTest', '质检',\n" +
                "              'termination', '终止',\n" +
                "              'receivables', '收款完成',\n" +
                "              '')                                                                                             SERVICE_STATE,\n" +
                "       (SELECT d.name FROM MAS_DEPT d WHERE d.ID = p.SALE_DEPT_ID)                                            pick_dept,\n" +
                "       (SELECT d.name\n" +
                "        FROM MAS_DEPT d\n" +
                "        WHERE d.ID = (CASE\n" +
                "                          WHEN o.INNER_DEPT_ID IS NOT NULL then o.INNER_DEPT_ID\n" +
                "                          ELSE p.SERVICE_DEPT_ID END))                                                        INNER_DEPT,\n" +
                "       (SELECT T.SPECIFICATIONS FROM MAS_PRODUCT T WHERE T.ID = I.PRODUCT_ID)                                 SPECIFICATIONS,\n" +
                "       (SELECT d.name FROM MAS_PRODUCT d WHERE d.ID = i.PRODUCT_ID)                                           PRODUCT_NAME\n" +
                "FROM SER_SERVICE_PLAN p,\n" +
                "     SER_SERVICE_PLAN_ITEM i,\n" +
                "     SER_MAINTENANCE_ORDER o,\n" +
                "     FN_CUSTOMER_EXPAND f\n" +
                "WHERE p.id = i.SERVICE_PLAN_ID\n" +
                "  AND f.ID = p.FN_CUSTOMER_EXPAND_ID\n" +
                "  AND o.SERVICE_PLAN_ITEM = i.ID\n" +
                "  AND o.WORK_STATE = 'finish'\n" +
                "  AND p.STATE = 'receivables'\n" +
                "  AND (SELECT A.CLASSIFY_ID FROM MAS_PRODUCT A WHERE A.ID = i.PRODUCT_ID) LIKE 'S0106%' ");
        JSONArray DATE_OF_STATISTICS = param.getJSONArray("DATE_OF_STATISTICS");
        try {
            JSONArray DATE_APPROVE_S = param.getJSONArray("DATE_APPROVE");
            if (DATE_APPROVE_S.size() == 2) {
                DATE_OF_STATISTICS = DATE_APPROVE_S;
                param.put("DATE_APPROVE", null);
            }
        } catch (Exception e) {

        }
        if (DATE_OF_STATISTICS!=null && param.get("DATE_OF_STATISTICS_STATE") == null) {
            String DATE_OF_STATISTICS_S = DATE_OF_STATISTICS.getString(0);
            Date DATE_OF_STATISTICS_STATE = StringToDate(DATE_OF_STATISTICS_S,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_STATE_S = DateUtil.format(DATE_OF_STATISTICS_STATE,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_STATE",DATE_OF_STATISTICS_STATE_S);
            String DATE_OF_STATISTICS_E = DATE_OF_STATISTICS.getString(1);
            Date DATE_OF_STATISTICS_END = StringToDate(DATE_OF_STATISTICS_E,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_END_S = DateUtil.format(DATE_OF_STATISTICS_END,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_END",DATE_OF_STATISTICS_END_S);
        }
        DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        if(param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_STATE") != null)  stringBuffer.append("   AND (\n" +
                "           O.FINISH_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd')\n" +
                "                )  ");
        if(param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_END") != null)  stringBuffer.append("   AND (\n" +
                "             O.FINISH_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1\n" +
                "                )  ");
        if(param.get("DEPT_ID") != null )  stringBuffer.append("    AND (i.PARENT_ITEM_ID IS NOT NULL AND p.BELONG_DEPT_ID = :DEPT_ID AND\n" +
                "                                              p.BELONG_DEPT_ID = p.SERVICE_DEPT_ID AND\n" +
                "                                              p.BELONG_DEPT_ID <> p.SALE_DEPT_ID)  ");

        if(param.get("SALE_DEPT_ID") != null )  stringBuffer.append("    AND P.SALE_DEPT_ID = :SALE_DEPT_ID  ");
        if(param.get("DATE_APPROVE") != null) {
            try {
                Date date = formatter.parse(param.getString("DATE_APPROVE"));
                stringBuffer.append("   AND (\n" +
                        "           O.FINISH_DATE >= to_date(:DATE_APPROVE,'yyyy-MM-dd')\n" +
                        "                )  ");
                stringBuffer.append("   AND (\n" +
                        "          O.FINISH_DATE < to_date(:DATE_APPROVE,'yyyy-MM-dd') + 1\n" +
                        "                )  ");

            } catch (Exception e) {
                Date start = formatter.parse(param.getString("DATE_OF_STATISTICS_STATE"));
                Date end = formatter.parse(param.getString("DATE_OF_STATISTICS_END"));
                LocalDate localDateStart = DateUtil.UDateToLocalDate(start);
                LocalDate localDateEnd = DateUtil.UDateToLocalDate(end);
                if (localDateStart.getMonthValue() == localDateEnd.getMonthValue()) {
                    stringBuffer.append("   AND (\n" +
                            "         O.FINISH_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd')\n" +
                            "                )  ");
                    stringBuffer.append("   AND (\n" +
                            "            O.FINISH_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1\n" +
                            "                )  ");
                } else {
                    formatter = new SimpleDateFormat("yyyy-MM");
                    Date dateApprove = formatter.parse(param.getString("DATE_APPROVE"));
                    LocalDate loDateApprove = DateUtil.UDateToLocalDate(dateApprove);
                    if (localDateStart.getMonthValue() == loDateApprove.getMonthValue()){
                        //获取月末
                        LocalDate monthOfLastDate = loDateApprove.with(TemporalAdjusters.lastDayOfMonth());
                        String SmonthOfLastDate = monthOfLastDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                        param.put("SmonthOfLastDate",SmonthOfLastDate);
                        stringBuffer.append("   AND (\n" +
                                "             O.FINISH_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd')\n" +
                                "                )  ");
                        stringBuffer.append("   AND (\n" +
                                "           O.FINISH_DATE < to_date(:SmonthOfLastDate,'yyyy-MM-dd') + 1\n" +
                                "                )  ");
                    } else {
                        //获取月头
                        LocalDate monthOfFirstDate = loDateApprove.with(TemporalAdjusters.firstDayOfMonth());
                        String SmonthOfFirstDate = monthOfFirstDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                        param.put("SmonthOfFirstDate",SmonthOfFirstDate);
                        stringBuffer.append("   AND (\n" +
                                "           O.FINISH_DATE >= to_date(:SmonthOfFirstDate,'yyyy-MM-dd')\n" +
                                "                )  ");
                        stringBuffer.append("   AND (\n" +
                                "         O.FINISH_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1\n" +
                                "                )  ");
                    }
                }

            }
        }
        return toJSONArray(find(stringBuffer.toString(), param));
    }
    /**
     * 喷漆发起幅数
     * @param param
     * @return
     * @throws ParseException
     */
    public JSONArray find_initPaint(JSONObject param) throws ParseException {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("SELECT SUM(CASE\n" +
                "               WHEN n.STATE = 'receivables' THEN (n.SPECIFICATIONS * n.SALE_QTY)\n" +
                "               ELSE 0 END)             QTY,\n" +
                "       (SELECT d.name FROM MAS_DEPT d WHERE d.ID = n.SERVICE_DEPT_ID) SERVICE_DEPT_NAME,SERVICE_DEPT_ID,\n" +
                "       (SELECT d.name FROM MAS_DEPT d WHERE d.ID = n.DEPT_ID) DEPT_NAME," +
                "       n.DEPT_ID, to_char(:DATE_OF_STATISTICS_STATE) DATE_OF_STATISTICS_STATE ,to_char(:DATE_OF_STATISTICS_END) DATE_OF_STATISTICS_END " +
                "FROM (SELECT p.ID,\n" +
                "             p.STATE,\n" +
                "             I.SALE_QTY,\n" +
                "             (SELECT T.SPECIFICATIONS FROM MAS_PRODUCT T WHERE T.ID = I.PRODUCT_ID)                  SPECIFICATIONS,\n" +
                "             ( CASE WHEN i.ASSIST_TYPE = 'ServicePlanItem.assistType.inner' THEN O.INNER_DEPT_ID ELSE p.BELONG_DEPT_ID END ) SERVICE_DEPT_ID,            (CASE\n" +
                "                  WHEN i.ASSIST_TYPE = 'ServicePlanItem.assistType.inner' then p.BELONG_DEPT_ID\n" +
                "                  ELSE p.SALE_DEPT_ID END) DEPT_ID \n" +
                "      FROM SER_SERVICE_PLAN p,\n" +
                "           SER_SERVICE_PLAN_ITEM i,\n" +
                "           SER_MAINTENANCE_ORDER o\n" +
                "      WHERE p.id = i.SERVICE_PLAN_ID\n" +
                "        AND o.SERVICE_PLAN_ITEM = i.ID\n" +
                "        AND o.WORK_STATE = 'finish'\n" +
                "        AND (SELECT A.CLASSIFY_ID FROM MAS_PRODUCT A WHERE A.ID = i.PRODUCT_ID) LIKE 'S0106%'");
        JSONArray DATE_OF_STATISTICS = param.getJSONArray("DATE_OF_STATISTICS");
        try {
            JSONArray DATE_APPROVE_S = param.getJSONArray("DATE_APPROVE");
            if (DATE_APPROVE_S.size() == 2) {
                DATE_OF_STATISTICS = DATE_APPROVE_S;
                param.put("DATE_APPROVE", null);
            }
        } catch (Exception e) {

        }
        if (DATE_OF_STATISTICS!=null) {
            String DATE_OF_STATISTICS_S = DATE_OF_STATISTICS.getString(0);
            Date DATE_OF_STATISTICS_STATE = StringToDate(DATE_OF_STATISTICS_S,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_STATE_S = DateUtil.format(DATE_OF_STATISTICS_STATE,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_STATE",DATE_OF_STATISTICS_STATE_S);
            String DATE_OF_STATISTICS_E = DATE_OF_STATISTICS.getString(1);
            Date DATE_OF_STATISTICS_END = StringToDate(DATE_OF_STATISTICS_E,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_END_S = DateUtil.format(DATE_OF_STATISTICS_END,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_END",DATE_OF_STATISTICS_END_S);
        }
        DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        if(param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_STATE") != null)  stringBuffer.append("   AND (\n" +
                "         O.FINISH_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd')\n" +
                "                )  ");
        if(param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_END") != null)  stringBuffer.append("   AND (\n" +
                "          O.FINISH_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1\n" +
                "                )  ");
        if(param.get("DEPT_ID") != null )  stringBuffer.append("     AND ((i.ASSIST_TYPE = 'ServicePlanItem.assistType.inner' AND p.BELONG_DEPT_ID =:DEPT_ID) OR\n" +
                "             (p.SALE_DEPT_ID <> p.SERVICE_DEPT_ID AND I.PARENT_ITEM_ID IS NULL  AND p.SALE_DEPT_ID = :DEPT_ID)) ");
        if(param.get("DATE_APPROVE") != null) {
            try {
                Date date = formatter.parse(param.getString("DATE_APPROVE"));
                stringBuffer.append("   AND (\n" +
                        "         O.FINISH_DATE >= to_date(:DATE_APPROVE,'yyyy-MM-dd')\n" +
                        "                )  ");
                stringBuffer.append("   AND (\n" +
                        "          O.FINISH_DATE < to_date(:DATE_APPROVE,'yyyy-MM-dd') + 1\n" +
                        "                )  ");

            } catch (Exception e) {
                Date start = formatter.parse(param.getString("DATE_OF_STATISTICS_STATE"));
                Date end = formatter.parse(param.getString("DATE_OF_STATISTICS_END"));
                LocalDate localDateStart = DateUtil.UDateToLocalDate(start);
                LocalDate localDateEnd = DateUtil.UDateToLocalDate(end);
                if (localDateStart.getMonthValue() == localDateEnd.getMonthValue()) {
                    stringBuffer.append("   AND (\n" +
                            "         O.FINISH_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd')\n" +
                            "                )  ");
                    stringBuffer.append("   AND (\n" +
                            "      O.FINISH_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1\n" +
                            "                )) ");
                } else {
                    formatter = new SimpleDateFormat("yyyy-MM");
                    Date dateApprove = formatter.parse(param.getString("DATE_APPROVE"));
                    LocalDate loDateApprove = DateUtil.UDateToLocalDate(dateApprove);
                    if (localDateStart.getMonthValue() == loDateApprove.getMonthValue()){
                        //获取月末
                        LocalDate monthOfLastDate = loDateApprove.with(TemporalAdjusters.lastDayOfMonth());
                        String SmonthOfLastDate = monthOfLastDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                        param.put("DATE_OF_STATISTICS_END",SmonthOfLastDate);
                        stringBuffer.append("   AND (\n" +
                                "           O.FINISH_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd')\n" +
                                "                )  ");
                        stringBuffer.append("   AND (\n" +
                                "         O.FINISH_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1\n" +
                                "                )  ");
                    } else {
                        //获取月头
                        LocalDate monthOfFirstDate = loDateApprove.with(TemporalAdjusters.firstDayOfMonth());
                        String SmonthOfFirstDate = monthOfFirstDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                        param.put("DATE_OF_STATISTICS_STATE",SmonthOfFirstDate);
                        stringBuffer.append("   AND (\n" +
                                "           O.FINISH_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd')\n" +
                                "                )  ");
                        stringBuffer.append("   AND (\n" +
                                "         O.FINISH_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1\n" +
                                "                )  ");
                    }
                }

            }
        }
        stringBuffer.append("     ) n\n" +
                "GROUP BY SERVICE_DEPT_ID,DEPT_ID ");
        return toJSONArray(find(stringBuffer.toString(), param));
    }


    /**
     * 喷漆发起幅数明细
     * @param param
     * @return
     * @throws ParseException
     */
    public JSONArray find_initPaintMX(JSONObject param) throws ParseException {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("SELECT p.ID,\n" +
                "       p.CAR_LISCENCE,\n" +
                "       TO_CHAR(O.FINISH_DATE, 'YYYY-MM-DD HH24:MI:SS')  FINISH_DATE,\n" +
                "       f.REALNAME,\n" +
                "       f.MOBILE,\n" +
                "       I.SALE_QTY ,\n" +
                "       I.PRICE_UNIT,\n" +
                "       I.PFT_PRICE_UNIT,\n" +
                "       i.REFER_TAX,\n" +
                "       DECODE(o.STATE, 'finish', '完成', '')                                                                    MAINTENANCE_ORDER_STATE,\n" +
                "       DECODE(p.STATE, 'confirm', '确认',\n" +
                "              'qualityTest', '质检',\n" +
                "              'termination', '终止',\n" +
                "              'receivables', '收款完成',\n" +
                "              '')                                                                                             SERVICE_STATE,\n" +
                "       (SELECT d.name FROM MAS_DEPT d WHERE d.ID = p.SALE_DEPT_ID)                                            pick_dept,\n" +
                "       (SELECT d.name\n" +
                "        FROM MAS_DEPT d\n" +
                "        WHERE d.ID = (CASE\n" +
                "                          WHEN o.INNER_DEPT_ID IS NOT NULL then o.INNER_DEPT_ID\n" +
                "                          ELSE p.SERVICE_DEPT_ID END))                                                        INNER_DEPT,\n" +
                "       (SELECT T.SPECIFICATIONS FROM MAS_PRODUCT T WHERE T.ID = I.PRODUCT_ID)                                 SPECIFICATIONS,\n" +
                "       (SELECT d.name FROM MAS_PRODUCT d WHERE d.ID = i.PRODUCT_ID)                                           PRODUCT_NAME\n" +
                "FROM SER_SERVICE_PLAN p,\n" +
                "     SER_SERVICE_PLAN_ITEM i,\n" +
                "     SER_MAINTENANCE_ORDER o,\n" +
                "     FN_CUSTOMER_EXPAND f\n" +
                "WHERE p.id = i.SERVICE_PLAN_ID\n" +
                "  AND f.ID = p.FN_CUSTOMER_EXPAND_ID\n" +
                "  AND o.SERVICE_PLAN_ITEM = i.ID\n" +
                "  AND o.WORK_STATE = 'finish'\n" +
                "  AND p.STATE = 'receivables'\n" +
                "  AND (SELECT A.CLASSIFY_ID FROM MAS_PRODUCT A WHERE A.ID = i.PRODUCT_ID) LIKE 'S0106%'");
        JSONArray DATE_OF_STATISTICS = param.getJSONArray("DATE_OF_STATISTICS");
        try {
            JSONArray DATE_APPROVE_S = param.getJSONArray("DATE_APPROVE");
            if (DATE_APPROVE_S.size() == 2) {
                DATE_OF_STATISTICS = DATE_APPROVE_S;
                param.put("DATE_APPROVE", null);
            }
        } catch (Exception e) {

        }
        if (DATE_OF_STATISTICS!=null&& param.get("DATE_OF_STATISTICS_STATE") == null) {
            String DATE_OF_STATISTICS_S = DATE_OF_STATISTICS.getString(0);
            Date DATE_OF_STATISTICS_STATE = StringToDate(DATE_OF_STATISTICS_S,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_STATE_S = DateUtil.format(DATE_OF_STATISTICS_STATE,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_STATE",DATE_OF_STATISTICS_STATE_S);
            String DATE_OF_STATISTICS_E = DATE_OF_STATISTICS.getString(1);
            Date DATE_OF_STATISTICS_END = StringToDate(DATE_OF_STATISTICS_E,DateUtil.SHOW_DATETIME_UTC_FORMAT);
            String DATE_OF_STATISTICS_END_S = DateUtil.format(DATE_OF_STATISTICS_END,DateUtil.SHOW_DATE_FORMAT);
            param.put("DATE_OF_STATISTICS_END",DATE_OF_STATISTICS_END_S);
        }
        DateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
        if(param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_STATE") != null)  stringBuffer.append("   AND (\n" +
                "           O.FINISH_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd')\n" +
                "                )  ");
        if(param.get("DATE_APPROVE") == null && param.get("DATE_OF_STATISTICS_END") != null)  stringBuffer.append("   AND (\n" +
                "         O.FINISH_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1\n" +
                "                )  ");
        if(param.get("DEPT_ID") != null && param.get("SERVICE_DEPT_ID") != null )  stringBuffer.append("     AND ((i.ASSIST_TYPE = 'ServicePlanItem.assistType.inner' AND p.BELONG_DEPT_ID =:DEPT_ID AND O.INNER_DEPT_ID = :SERVICE_DEPT_ID) OR\n" +
                "             (p.SALE_DEPT_ID <> p.SERVICE_DEPT_ID AND I.PARENT_ITEM_ID IS NULL AND p.SALE_DEPT_ID = :DEPT_ID AND p.BELONG_DEPT_ID = :SERVICE_DEPT_ID)) ");

//        if(param.get("SERVICE_DEPT_ID") != null )  stringBuffer.append("  AND O.INNER_DEPT_ID =:SERVICE_DEPT_ID ");
        if(param.get("DATE_APPROVE") != null) {
            try {
                Date date = formatter.parse(param.getString("DATE_APPROVE"));
                stringBuffer.append("   AND (\n" +
                        "            O.FINISH_DATE >= to_date(:DATE_APPROVE,'yyyy-MM-dd')\n" +
                        "                )  ");
                stringBuffer.append("   AND (\n" +
                        "         O.FINISH_DATE < to_date(:DATE_APPROVE,'yyyy-MM-dd') + 1\n" +
                        "                )  ");

            } catch (Exception e) {
                Date start = formatter.parse(param.getString("DATE_OF_STATISTICS_STATE"));
                Date end = formatter.parse(param.getString("DATE_OF_STATISTICS_END"));
                LocalDate localDateStart = DateUtil.UDateToLocalDate(start);
                LocalDate localDateEnd = DateUtil.UDateToLocalDate(end);
                if (localDateStart.getMonthValue() == localDateEnd.getMonthValue()) {
                    stringBuffer.append("   AND (\n" +
                            "           O.FINISH_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd')\n" +
                            "                )  ");
                    stringBuffer.append("   AND (\n" +
                            "         O.FINISH_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1\n" +
                            "                )  ");
                } else {
                    formatter = new SimpleDateFormat("yyyy-MM");
                    Date dateApprove = formatter.parse(param.getString("DATE_APPROVE"));
                    LocalDate loDateApprove = DateUtil.UDateToLocalDate(dateApprove);
                    if (localDateStart.getMonthValue() == loDateApprove.getMonthValue()){
                        //获取月末
                        LocalDate monthOfLastDate = loDateApprove.with(TemporalAdjusters.lastDayOfMonth());
                        String SmonthOfLastDate = monthOfLastDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                        param.put("SmonthOfLastDate",SmonthOfLastDate);
                        stringBuffer.append("   AND (\n" +
                                "            O.FINISH_DATE >= to_date(:DATE_OF_STATISTICS_STATE,'yyyy-MM-dd')\n" +
                                "                )  ");
                        stringBuffer.append("   AND (\n" +
                                "             O.FINISH_DATE < to_date(:SmonthOfLastDate,'yyyy-MM-dd') + 1\n" +
                                "                )  ");
                    } else {
                        //获取月头
                        LocalDate monthOfFirstDate = loDateApprove.with(TemporalAdjusters.firstDayOfMonth());
                        String SmonthOfFirstDate = monthOfFirstDate.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                        param.put("SmonthOfFirstDate",SmonthOfFirstDate);
                        stringBuffer.append("   AND (\n" +
                                "            O.FINISH_DATE >= to_date(:SmonthOfFirstDate,'yyyy-MM-dd')\n" +
                                "                )  ");
                        stringBuffer.append("   AND (\n" +
                                "           O.FINISH_DATE < to_date(:DATE_OF_STATISTICS_END,'yyyy-MM-dd') + 1\n" +
                                "                )  ");
                    }
                }

            }
        }
        return toJSONArray(find(stringBuffer.toString(), param));
    }
    private JSONArray printSql(JSONObject param){
        JSONArray jsonArray = new JSONArray();
        if (param.getBoolean("Data")!=null&&param.getBoolean("Data")) {
            jsonArray.add(param.getString("data"));
        }
        return jsonArray;
    }


    private String getRCL_DAILYSALESREPORTView(JSONObject param,String STARTDATE,String ENDDATE) {
        StringBuffer stringBuffer = new StringBuffer();
        stringBuffer.append("select company_id,\n" +
                "       dept_id,\n" +
                "       sum(sale_money )sale_money ,\n" +
                "       sum(payment_money )payment_money ,\n" +
                "       sum(accounts_receivablein )accounts_receivablein ,\n" +
                "       sum(accounts_receivableout )accounts_receivableout ,\n" +
                "       sum(yearcard )yearcard ,\n" +
                "       sum(advance_payment )advance_payment ,\n" +
                "       sum(stored_value )stored_value ,\n" +
                "       sum(buy_yearcard )buy_yearcard ,\n" +
                "       sum(stored_value_recharge )stored_value_recharge ,\n" +
                "       sum(advance_payment_recharge )advance_payment_recharge ,\n" +
                "       sum(fixed_loss_collection )fixed_loss_collection ,\n" +
                "       sum(capital_income )capital_income ,\n" +
                "       sum(service_performance )service_performance ,\n" +
                "       sum(times_of_entering_the_store )times_of_entering_the_store ,\n" +
                "       sum(car_wash )car_wash ,\n" +
                "       sum(car_wash_times )car_wash_times ,\n" +
                "       sum(cosmetology )cosmetology ,\n" +
                "       sum(cosmetology_times )cosmetology_times ,\n" +
                "       sum(maintain )maintain ,\n" +
                "       sum(maintain_times )maintain_times ,\n" +
                "       sum(tyre )tyre ,\n" +
                "       sum(tyre_times )tyre_times ,\n" +
                "       sum(repair )repair ,\n" +
                "       sum(repair_times )repair_times ,\n" +
                "       sum(sheet_metal_spray )sheet_metal_spray ,\n" +
                "       sum(sheet_metal_spray_times )sheet_metal_spray_times ,\n" +
                "       sum(refit )refit ,\n" +
                "       sum(refit_times )refit_times ,\n" +
                "       sum(road_rescue )road_rescue ,\n" +
                "       sum(road_rescue_times )road_rescue_times ,\n" +
                "       sum(boutique )boutique ,\n" +
                "       sum(articles_for_daily_use )articles_for_daily_use ,\n" +
                "       sum(technical_inspection )technical_inspection ,\n" +
                "       sum(information_service )information_service ,\n" +
                "       sum(fixed_loss_direct_compensation )fixed_loss_direct_compensation ,\n" +
                "       sum(stored_value_retail )stored_value_retail ,\n" +
                "       sum(insurance_order_amt )insurance_order_amt ,\n" +
                "       sum(coupon_replacement )coupon_replacement ,\n" +
                "       sum(user_car_fee )user_car_fee ,\n" +
                "       sum(fn_commission )fn_commission ,\n" +
                "       sum(fn_settle_expens )fn_settle_expens ,\n" +
                "       sum(userd_car_final )userd_car_final ,\n" +
                "       sum(insurance_amount )insurance_amount ,\n" +
                "       sum(our_loss_collection )our_loss_collection ,\n" +
                "       sum(inner_loss_collection )inner_loss_collection ,\n" +
                "       sum(small_maintain )small_maintain ,\n" +
                "       sum(small_maintain_times )small_maintain_times ,\n" +
                "       sum(cross_company_income )cross_company_income ,\n" +
                "       sum(cross_company_pay )cross_company_pay ,\n" +
                "       sum(spray_paint_number )spray_paint_number ,\n" +
                "       sum(our_loss_times )our_loss_times ,\n" +
                "       sum(inner_loss_times )inner_loss_times ,\n" +
                "       sum(out_paint_count )out_paint_count ,\n" +
                "       sum(inner_orig_count )inner_orig_count ,\n" +
                "       sum(inner_not_orig_count )inner_not_orig_count ,\n" +
                "       sum(inner_receive_count )inner_receive_count ,\n" +
                "       sum(inner_not_receive_count )inner_not_receive_count ,\n" +
                "       sum(buy_beauty_year_card )buy_beauty_year_card ,\n" +
                "       sum(buy_maintain_year_card )buy_maintain_year_card ,\n" +
                "       sum(e_maintain )e_maintain ,\n" +
                "       sum(commission )commission ,\n" +
                "       sum(annual_audit )annual_audit ,\n" +
                "       sum(inner_loss_collection_md )inner_loss_collection_md ,\n" +
                "       sum(self_fixed_loss )self_fixed_loss ,\n" +
                "       sum(service_performance_self )service_performance_self ,\n" +
                "       sum(per_service_replace_tgs )per_service_replace_tgs ,\n" +
                "       sum(per_service_replace_kgs )per_service_replace_kgs ,\n" +
                "       sum(per_service_replace )per_service_replace ,\n" +
                "       sum(fn_settle_expense_tgs)fn_settle_expense_tgs\n" +
                "from RCL_DAILYSALESREPORT A\n" +
                " where 1=1 "
        );
        if (param.get(STARTDATE) != null) stringBuffer.append(" AND A.DATE_OF_STATISTICS >= :"+STARTDATE+" ");
        if (param.get(ENDDATE) != null) stringBuffer.append(" AND A.DATE_OF_STATISTICS <= :"+ENDDATE+" ");
        stringBuffer.append("group by company_id, dept_id");
        return stringBuffer.toString();

    }
}
